<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  

  
  <title>数据库-性能优化 | Threadv</title>
  <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
  <meta name="description" content="1、   为什么要进行数据库优化？1.1、 避免网站页面出现访问错误由于数据库连接timeout产生页面5xx错误 由于慢查询造成页面无法加载 由于阻塞造成数据无法提交 1.2、 增加数据库的稳定性很多数据库问题都是由于低效的查询引起的 1.3、 优化用户体验流畅页面的访问速度 良好的网站功能体验 2、mysql数据库">
<meta name="keywords" content="mysql">
<meta property="og:type" content="article">
<meta property="og:title" content="数据库-性能优化">
<meta property="og:url" content="http://yoursite.com/2019/01/14/mysql/sql_数据库性能优化/index.html">
<meta property="og:site_name" content="Threadv">
<meta property="og:description" content="1、   为什么要进行数据库优化？1.1、 避免网站页面出现访问错误由于数据库连接timeout产生页面5xx错误 由于慢查询造成页面无法加载 由于阻塞造成数据无法提交 1.2、 增加数据库的稳定性很多数据库问题都是由于低效的查询引起的 1.3、 优化用户体验流畅页面的访问速度 良好的网站功能体验 2、mysql数据库优化可以从哪几个方面进行数据库的优化？如下图所示：  2.1、 SQL及索引优化">
<meta property="og:image" content="file:///C:/Users/Deborah/AppData/Local/Temp/msohtmlclip1/01/clip_image002.png">
<meta property="og:image" content="file:///C:/Users/Deborah/AppData/Local/Temp/msohtmlclip1/01/clip_image018.png">
<meta property="og:image" content="file:///C:/Users/Deborah/AppData/Local/Temp/msohtmlclip1/01/clip_image020.png">
<meta property="og:image" content="file:///C:/Users/Deborah/AppData/Local/Temp/msohtmlclip1/01/clip_image087.jpg">
<meta property="og:image" content="file:///C:/Users/Deborah/AppData/Local/Temp/msohtmlclip1/01/clip_image089.jpg">
<meta property="og:image" content="file:///C:/Users/Deborah/AppData/Local/Temp/msohtmlclip1/01/clip_image091.jpg">
<meta property="og:image" content="file:///C:/Users/Deborah/AppData/Local/Temp/msohtmlclip1/01/clip_image093.jpg">
<meta property="og:updated_time" content="2020-03-29T11:01:38.428Z">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="数据库-性能优化">
<meta name="twitter:description" content="1、   为什么要进行数据库优化？1.1、 避免网站页面出现访问错误由于数据库连接timeout产生页面5xx错误 由于慢查询造成页面无法加载 由于阻塞造成数据无法提交 1.2、 增加数据库的稳定性很多数据库问题都是由于低效的查询引起的 1.3、 优化用户体验流畅页面的访问速度 良好的网站功能体验 2、mysql数据库优化可以从哪几个方面进行数据库的优化？如下图所示：  2.1、 SQL及索引优化">
<meta name="twitter:image" content="file:///C:/Users/Deborah/AppData/Local/Temp/msohtmlclip1/01/clip_image002.png">
  
    <link rel="alternate" href="/atom.xml" title="Threadv" type="application/atom+xml">
  
  
    <link rel="icon" href="/favicon.png">
  
  
    <link href="//fonts.googleapis.com/css?family=Source+Code+Pro" rel="stylesheet" type="text/css">
  
  <link rel="stylesheet" href="/css/style.css">
</head>

<body>
  <div id="container">
    <div id="wrap">
      <header id="header">
  <div id="banner"></div>
  <div id="header-outer" class="outer">
    <div id="header-title" class="inner">
      <h1 id="logo-wrap">
        <a href="/" id="logo">Threadv</a>
      </h1>
      
    </div>
    <div id="header-inner" class="inner">
      <nav id="main-nav">
        <a id="main-nav-toggle" class="nav-icon"></a>
        
          <a class="main-nav-link" href="/">Home</a>
        
          <a class="main-nav-link" href="/archives">Archives</a>
        
      </nav>
      <nav id="sub-nav">
        
          <a id="nav-rss-link" class="nav-icon" href="/atom.xml" title="RSS Feed"></a>
        
        <a id="nav-search-btn" class="nav-icon" title="Suche"></a>
      </nav>
      <div id="search-form-wrap">
        <form action="//google.com/search" method="get" accept-charset="UTF-8" class="search-form"><input type="search" name="q" class="search-form-input" placeholder="Search"><button type="submit" class="search-form-submit">&#xF002;</button><input type="hidden" name="sitesearch" value="http://yoursite.com"></form>
      </div>
    </div>
  </div>
</header>
      <div class="outer">
        <section id="main"><article id="post-mysql/sql_数据库性能优化" class="article article-type-post" itemscope itemprop="blogPost">
  <div class="article-meta">
    <a href="/2019/01/14/mysql/sql_数据库性能优化/" class="article-date">
  <time datetime="2019-01-14T02:43:14.000Z" itemprop="datePublished">2019-01-14</time>
</a>
    
  </div>
  <div class="article-inner">
    
    
      <header class="article-header">
        
  
    <h1 class="article-title" itemprop="name">
      数据库-性能优化
    </h1>
  

      </header>
    
    <div class="article-entry" itemprop="articleBody">
      
        <h2 id="1、-为什么要进行数据库优化？"><a href="#1、-为什么要进行数据库优化？" class="headerlink" title="1、   为什么要进行数据库优化？"></a>1、   为什么要进行数据库优化？</h2><h3 id="1-1、-避免网站页面出现访问错误"><a href="#1-1、-避免网站页面出现访问错误" class="headerlink" title="1.1、 避免网站页面出现访问错误"></a>1.1、 避免网站页面出现访问错误</h3><p>由于数据库连接timeout产生页面5xx错误</p>
<p>由于慢查询造成页面无法加载</p>
<p>由于阻塞造成数据无法提交</p>
<h3 id="1-2、-增加数据库的稳定性"><a href="#1-2、-增加数据库的稳定性" class="headerlink" title="1.2、 增加数据库的稳定性"></a>1.2、 增加数据库的稳定性</h3><p>很多数据库问题都是由于低效的查询引起的</p>
<h3 id="1-3、-优化用户体验"><a href="#1-3、-优化用户体验" class="headerlink" title="1.3、 优化用户体验"></a>1.3、 优化用户体验</h3><p>流畅页面的访问速度</p>
<p>良好的网站功能体验</p>
<h2 id="2、mysql数据库优化"><a href="#2、mysql数据库优化" class="headerlink" title="2、mysql数据库优化"></a>2、mysql数据库优化</h2><p>可以从哪几个方面进行数据库的优化？如下图所示：</p>
<p><img src="file:///C:/Users/Deborah/AppData/Local/Temp/msohtmlclip1/01/clip_image002.png" alt=""></p>
<h3 id="2-1、-SQL及索引优化"><a href="#2-1、-SQL及索引优化" class="headerlink" title="2.1、 SQL及索引优化"></a>2.1、 SQL及索引优化</h3><p>​    根据需求写出良好的SQL，并创建有效的索引，实现某一种需求可以多种写法，这时候我们就要选择一种效率最高的写法。这个时候就要了解sql优化</p>
<h3 id="2-2、-数据库表结构优化"><a href="#2-2、-数据库表结构优化" class="headerlink" title="2.2、 数据库表结构优化"></a>2.2、 数据库表结构优化</h3><p>根据数据库的范式，设计表结构，表结构设计的好直接关系到写SQL语句。</p>
<h3 id="2-3、-系统配置优化"><a href="#2-3、-系统配置优化" class="headerlink" title="2.3、 系统配置优化"></a>2.3、 系统配置优化</h3><p>大多数运行在Linux机器上，如tcp连接数的限制、打开文件数的限制、安全性的限制，因此我们要对这些配置进行相应的优化。</p>
<h3 id="2-4、硬件配置优化"><a href="#2-4、硬件配置优化" class="headerlink" title="2.4、硬件配置优化"></a>2.4、硬件配置优化</h3><p>选择适合数据库服务的cpu，更快的IO，更高的内存；cpu并不是越多越好，某些数据库版本有最大的限制，IO操作并不是减少阻塞。</p>
<p>注：通过上图可以看出，该金字塔中，优化的成本从下而上逐渐增高，而优化的效果会逐渐降低。</p>
<h2 id="3、SQL及索引优化"><a href="#3、SQL及索引优化" class="headerlink" title="3、SQL及索引优化"></a>3、SQL及索引优化</h2><h3 id="3-1、查看mysql的版本"><a href="#3-1、查看mysql的版本" class="headerlink" title="3.1、查看mysql的版本"></a>3.1、查看mysql的版本</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">select @@version;</span><br><span class="line"></span><br><span class="line">mysql&gt; select @@version;</span><br><span class="line">+-----------+</span><br><span class="line">| @@version |</span><br><span class="line">+-----------+</span><br><span class="line">| 5.6.25    |</span><br><span class="line">+-----------+</span><br><span class="line">1 row in set (0.00 sec)</span><br></pre></td></tr></table></figure>
<h3 id="3-2、准备数据"><a href="#3-2、准备数据" class="headerlink" title="3.2、准备数据"></a>3.2、准备数据</h3><p><strong>网址：<a href="https://dev.mysql.com/doc/sakila/en/sakila-installation.html" target="_blank" rel="noopener">https://dev.mysql.com/doc/sakila/en/sakila-installation.html</a></strong></p>
<p> 步骤：</p>
<p>1、通过命令行来连接数据库</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">shell&gt; mysql -u root -p</span><br></pre></td></tr></table></figure>
<p>2、创建表及语句执行</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; SOURCE C:/temp/sakila-db/sakila-schema.sql;</span><br></pre></td></tr></table></figure>
<p>3、加载数据</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; SOURCE C:/temp/sakila-db/sakila-data.sql;</span><br></pre></td></tr></table></figure>
<p>4、使用数据库</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">USE sakila;</span><br></pre></td></tr></table></figure>
<p>5、检查创建的表</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br></pre></td><td class="code"><pre><span class="line">SHOW TABLES;</span><br><span class="line"></span><br><span class="line">+----------------------------+</span><br><span class="line">| Tables_in_sakila           |</span><br><span class="line">+----------------------------+</span><br><span class="line">| actor                      |</span><br><span class="line">| actor_info                 |</span><br><span class="line">| address                    |</span><br><span class="line">| category                   |</span><br><span class="line">| city                       |</span><br><span class="line">| country                    |</span><br><span class="line">| customer                   |</span><br><span class="line">| customer_list              |</span><br><span class="line">| film                       |</span><br><span class="line">| film_actor                 |</span><br><span class="line">| film_category              |</span><br><span class="line">| film_list                  |</span><br><span class="line">| film_text                  |</span><br><span class="line">| inventory                  |</span><br><span class="line">| language                   |</span><br><span class="line">| nicer_but_slower_film_list |</span><br><span class="line">| payment                    |</span><br><span class="line">| rental                     |</span><br><span class="line">| sales_by_film_category     |</span><br><span class="line">| sales_by_store             |</span><br><span class="line">| staff                      |</span><br><span class="line">| staff_list                 |</span><br><span class="line">| store                      |</span><br><span class="line">+----------------------------+</span><br></pre></td></tr></table></figure>
<p>6、检验数据是否加载进去</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; select count(*) from film;</span><br><span class="line">+----------+</span><br><span class="line">| count(*) |</span><br><span class="line">+----------+</span><br><span class="line">|     1000 |</span><br><span class="line">+----------+</span><br><span class="line">1 row in set (0.00 sec)</span><br><span class="line"></span><br><span class="line"></span><br><span class="line">mysql&gt; select count(*) from payment ;</span><br><span class="line">+----------+</span><br><span class="line">| count(*) |</span><br><span class="line">+----------+</span><br><span class="line">|    16049 |</span><br><span class="line">+----------+</span><br><span class="line">1 row in set (0.00 sec)</span><br><span class="line"></span><br><span class="line">mysql&gt; select count(*) from staff ;</span><br><span class="line">+----------+</span><br><span class="line">| count(*) |</span><br><span class="line">+----------+</span><br><span class="line">|        2 |</span><br><span class="line">+----------+</span><br><span class="line">1 row in set (0.00 sec)</span><br><span class="line"></span><br><span class="line">mysql&gt; select count(*) from store;</span><br><span class="line">+----------+</span><br><span class="line">| count(*) |</span><br><span class="line">+----------+</span><br><span class="line">|        2 |</span><br><span class="line">+----------+</span><br><span class="line">1 row in set (0.00 sec)</span><br></pre></td></tr></table></figure>
<p>注：该表结构关系是用工具生成的。（powerDisigner）</p>
<h3 id="3-3、如何发现有问题的SQL"><a href="#3-3、如何发现有问题的SQL" class="headerlink" title="3.3、如何发现有问题的SQL"></a>3.3、如何发现有问题的SQL</h3><p>MySQL慢查日志的开启方式和存储格式</p>
<h4 id="3-3-1、检查慢查日志是否开启："><a href="#3-3-1、检查慢查日志是否开启：" class="headerlink" title="3.3.1、检查慢查日志是否开启："></a>3.3.1、检查慢查日志是否开启：</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">_query_log&apos;</span><br><span class="line"></span><br><span class="line">mysql&gt; show variables like &apos;slow_query_log&apos;;</span><br><span class="line">+----------------+-------+</span><br><span class="line">| Variable_name  | Value |</span><br><span class="line">+----------------+-------+</span><br><span class="line">| slow_query_log | OFF   |</span><br><span class="line">+----------------+-------+</span><br><span class="line">1 row in set (0.00 sec)</span><br></pre></td></tr></table></figure>
<h4 id="3-3-2、查看所有日志的变量信息"><a href="#3-3-2、查看所有日志的变量信息" class="headerlink" title="3.3.2、查看所有日志的变量信息"></a>3.3.2、查看所有日志的变量信息</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br><span class="line">70</span><br><span class="line">71</span><br><span class="line">72</span><br><span class="line">73</span><br><span class="line">74</span><br><span class="line">75</span><br><span class="line">76</span><br><span class="line">77</span><br><span class="line">78</span><br><span class="line">79</span><br><span class="line">80</span><br><span class="line">81</span><br><span class="line">82</span><br><span class="line">83</span><br><span class="line">84</span><br><span class="line">85</span><br><span class="line">86</span><br><span class="line">87</span><br><span class="line">88</span><br><span class="line">89</span><br><span class="line">90</span><br><span class="line">91</span><br><span class="line">92</span><br><span class="line">93</span><br><span class="line">94</span><br><span class="line">95</span><br><span class="line">96</span><br><span class="line">97</span><br><span class="line">98</span><br><span class="line">99</span><br><span class="line">100</span><br><span class="line">101</span><br><span class="line">102</span><br><span class="line">103</span><br><span class="line">104</span><br><span class="line">105</span><br><span class="line">106</span><br><span class="line">107</span><br><span class="line">108</span><br><span class="line">109</span><br><span class="line">110</span><br><span class="line">111</span><br><span class="line">112</span><br><span class="line">113</span><br><span class="line">114</span><br><span class="line">115</span><br><span class="line">116</span><br><span class="line">117</span><br><span class="line">118</span><br><span class="line">119</span><br><span class="line">120</span><br><span class="line">121</span><br><span class="line">122</span><br><span class="line">123</span><br><span class="line">124</span><br><span class="line">125</span><br><span class="line">126</span><br><span class="line">127</span><br><span class="line">128</span><br><span class="line">129</span><br><span class="line">130</span><br><span class="line">131</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; show variables like &apos;%log%&apos;;</span><br><span class="line"></span><br><span class="line">+-----------------------------------------+------------------------------------+</span><br><span class="line"></span><br><span class="line">| Variable_name                           | Value                              |</span><br><span class="line"></span><br><span class="line">+-----------------------------------------+------------------------------------+</span><br><span class="line"></span><br><span class="line">| back_log                               |80                                 |</span><br><span class="line"></span><br><span class="line">| binlog_cache_size                       | 32768                              |</span><br><span class="line"></span><br><span class="line">| binlog_checksum                         | CRC32                              |</span><br><span class="line"></span><br><span class="line">| binlog_direct_non_transactional_updates |OFF                                |</span><br><span class="line"></span><br><span class="line">| binlog_error_action                     | IGNORE_ERROR                       |</span><br><span class="line"></span><br><span class="line">| binlog_format                           | STATEMENT                          |</span><br><span class="line"></span><br><span class="line">| binlog_gtid_simple_recovery             | OFF                                |</span><br><span class="line"></span><br><span class="line">| binlog_max_flush_queue_time             | 0                                  |</span><br><span class="line"></span><br><span class="line">| binlog_order_commits                    | ON                                 |</span><br><span class="line"></span><br><span class="line">| binlog_row_image                        | FULL                               |</span><br><span class="line"></span><br><span class="line">| binlog_rows_query_log_events            | OFF                                |</span><br><span class="line"></span><br><span class="line">| binlog_stmt_cache_size                  | 32768                              |</span><br><span class="line"></span><br><span class="line">| binlogging_impossible_mode              | IGNORE_ERROR                       |</span><br><span class="line"></span><br><span class="line">| expire_logs_days                        | 0                                  |</span><br><span class="line"></span><br><span class="line">| general_log                             | OFF                                |</span><br><span class="line"></span><br><span class="line">| general_log_file                        |/var/lib/mysql/mysql-host.log      |</span><br><span class="line"></span><br><span class="line">| innodb_api_enable_binlog                | OFF                                |</span><br><span class="line"></span><br><span class="line">| innodb_flush_log_at_timeout             | 1                                  |</span><br><span class="line"></span><br><span class="line">| innodb_flush_log_at_trx_commit          | 1                                  |</span><br><span class="line"></span><br><span class="line">| innodb_locks_unsafe_for_binlog          | OFF                                |</span><br><span class="line"></span><br><span class="line">| innodb_log_buffer_size                  | 8388608                            |</span><br><span class="line"></span><br><span class="line">| innodb_log_compressed_pages             | ON                                 |</span><br><span class="line"></span><br><span class="line">| innodb_log_file_size                    | 50331648                           |</span><br><span class="line"></span><br><span class="line">| innodb_log_files_in_group               | 2                                  |</span><br><span class="line"></span><br><span class="line">| innodb_log_group_home_dir               | ./                                 |</span><br><span class="line"></span><br><span class="line">| innodb_mirrored_log_groups              | 1                                  |</span><br><span class="line"></span><br><span class="line">| innodb_online_alter_log_max_size        | 134217728                          |</span><br><span class="line"></span><br><span class="line">| innodb_undo_logs                        | 128                                |</span><br><span class="line"></span><br><span class="line">| log_bin                                 | OFF                                |</span><br><span class="line"></span><br><span class="line">| log_bin_basename                        |                                    |</span><br><span class="line"></span><br><span class="line">| log_bin_index                           |                                    |</span><br><span class="line"></span><br><span class="line">| log_bin_trust_function_creators         | OFF                                |</span><br><span class="line"></span><br><span class="line">| log_bin_use_v1_row_events               | OFF                                |</span><br><span class="line"></span><br><span class="line">| log_error                               |/var/log/mysqld.log                |</span><br><span class="line"></span><br><span class="line">| log_output                              | FILE                               |</span><br><span class="line"></span><br><span class="line">| log_queries_not_using_indexes           | ON                                 |</span><br><span class="line"></span><br><span class="line">| log_slave_updates                       | OFF                                |</span><br><span class="line"></span><br><span class="line">| log_slow_admin_statements               | OFF                                |</span><br><span class="line"></span><br><span class="line">| log_slow_slave_statements               | OFF                                |</span><br><span class="line"></span><br><span class="line">|log_throttle_queries_not_using_indexes  |0                                  |</span><br><span class="line"></span><br><span class="line">| log_warnings                            | 1                                  |</span><br><span class="line"></span><br><span class="line">| max_binlog_cache_size                   | 18446744073709547520               |</span><br><span class="line"></span><br><span class="line">| max_binlog_size                         | 1073741824                         |</span><br><span class="line"></span><br><span class="line">| max_binlog_stmt_cache_size              | 18446744073709547520               |</span><br><span class="line"></span><br><span class="line">| max_relay_log_size                      | 0                                  |</span><br><span class="line"></span><br><span class="line">| relay_log                               |                                    |</span><br><span class="line"></span><br><span class="line">| relay_log_basename                      |                                    |</span><br><span class="line"></span><br><span class="line">| relay_log_index                         |                                    |</span><br><span class="line"></span><br><span class="line">| relay_log_info_file                     | relay-log.info                     |</span><br><span class="line"></span><br><span class="line">| relay_log_info_repository               | FILE                               |</span><br><span class="line"></span><br><span class="line">| relay_log_purge                         | ON                                 |</span><br><span class="line"></span><br><span class="line">| relay_log_recovery                      | OFF                                |</span><br><span class="line"></span><br><span class="line">| relay_log_space_limit                   | 0                                  |</span><br><span class="line"></span><br><span class="line">| simplified_binlog_gtid_recovery         | OFF                                |</span><br><span class="line"></span><br><span class="line">|slow_query_log                          |OFF                                |</span><br><span class="line"></span><br><span class="line">| slow_query_log_file                     |/var/lib/mysql/mysql-host-slow.log |</span><br><span class="line"></span><br><span class="line">| sql_log_bin                             | ON                                 |</span><br><span class="line"></span><br><span class="line">| sql_log_off                             | OFF                                |</span><br><span class="line"></span><br><span class="line">| sync_binlog                             | 0                                  |</span><br><span class="line"></span><br><span class="line">| sync_relay_log                          | 10000                              |</span><br><span class="line"></span><br><span class="line">| sync_relay_log_info                     | 10000                              |</span><br><span class="line"></span><br><span class="line">+-----------------------------------------+------------------------------------+</span><br></pre></td></tr></table></figure>
<p>61 rows in set (0.01 sec)</p>
<p>开启慢查日志：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">show variables like &apos;%log%&apos;</span><br></pre></td></tr></table></figure>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line">log_queries_not_using_indexes           | ON                            |</span><br><span class="line"></span><br><span class="line"> slow_query_log                          | OFF                           |</span><br><span class="line">| slow_query_log_file                     | /var/lib/mysql/mysql-slow.log |</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"></span><br><span class="line">//通过这个命令来设置开启慢日志</span><br><span class="line">mysql&gt; set global slow_query_log=on;</span><br><span class="line">Query OK, 0 rows affected (0.32 sec)</span><br></pre></td></tr></table></figure>
<p>验证慢查询日志是否开启：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"># Time: 181026  0:39:29</span><br><span class="line"># User@Host: root[root] @ localhost []  Id:     3</span><br><span class="line"># Query_time: 0.000098  Lock_time: 0.000050 Rows_sent: 1  Rows_examined: 2</span><br><span class="line">SET timestamp=1540485569;</span><br><span class="line">select count(*) from staff;</span><br></pre></td></tr></table></figure>
<p>在mysql操作中，</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">Show databases;</span><br><span class="line"></span><br><span class="line">Use sakila;</span><br><span class="line"></span><br><span class="line">select * from store;</span><br><span class="line"></span><br><span class="line">select * from staff;</span><br></pre></td></tr></table></figure>
<p>监听日志文件，看是否写入</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">tail -f /var/lib/mysql/mysql-slow.log</span><br></pre></td></tr></table></figure>
<h4 id="3-3-3、MySQL慢查日志的存储格式"><a href="#3-3-3、MySQL慢查日志的存储格式" class="headerlink" title="3.3.3、MySQL慢查日志的存储格式"></a>3.3.3、MySQL慢查日志的存储格式</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">show variables like &apos;slow%&apos;;</span><br><span class="line"></span><br><span class="line">+---------------------+---------------------------------------------------------------------+</span><br><span class="line">| Variable_name       | Value                                                               |</span><br><span class="line">+---------------------+---------------------------------------------------------------------+</span><br><span class="line">| slow_launch_time    | 2                                                                   |</span><br><span class="line">| slow_query_log      | OFF                                                                 |</span><br><span class="line">| slow_query_log_file | C:\ProgramData\MySQL\MySQL Server 5.5\Data\DESKTOP-2LA2VDS-slow.log |</span><br><span class="line">+---------------------+---------------------------------------------------------------------+</span><br></pre></td></tr></table></figure>
<p>参数说明：</p>
<pre><code>1、# Time: 180526  1:06:54 -------à查询的执行时间

2、# User@Host: root[root] @ localhost []  Id:     4 -------à执行sql的主机信息

3、# Query_time: 0.000401 Lock_time: 0.000105 Rows_sent: 2 Rows_examined: 2-------àSQL的执行信息：

              Query_time：SQL的查询时间

              Lock_time：锁定时间

              Rows_sent：所发送的行数

              Rows_examined：锁扫描的行数

4、SET timestamp=1527268014; -------àSQL执行时间

5、select * from staff; -------àSQL的执行内容
</code></pre><h2 id="4、MySQL慢查日志分析工具（mysqldumpslow）"><a href="#4、MySQL慢查日志分析工具（mysqldumpslow）" class="headerlink" title="4、MySQL慢查日志分析工具（mysqldumpslow）"></a>4、MySQL慢查日志分析工具（mysqldumpslow）</h2><h3 id="1、介绍"><a href="#1、介绍" class="headerlink" title="1、介绍"></a>1、介绍</h3><p>如何进行查看慢查询日志，如果开启了慢查询日志，就会生成很多的数据，然后我们就可以通过对日志的分析，生成分析报表，然后通过报表进行优化。</p>
<h3 id="2、用法"><a href="#2、用法" class="headerlink" title="2、用法"></a>2、用法</h3><p>接下来我们查看一下这个工具的用法</p>
<p>注意：在mysql数据库所在的服务器上，而不是在mysql&gt;命令行中</p>
<p>该工具如何使用：<strong>mysqldumpslow -h</strong></p>
<p><strong>查看verbose信息</strong></p>
<p><strong>Mysqldumpslow -v</strong></p>
<p>查看慢查询日志的前10个，mysqldumpslow 分析的结果如下</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line">[root@mysql ~]# mysqldumpslow -t 10 /var/lib/mysql/mysql-slow.log</span><br><span class="line"></span><br><span class="line">Reading mysql slow query log from /var/lib/mysql/mysql-slow.log</span><br><span class="line">Count: 1  Time=0.01s (0s)  Lock=0.00s (0s)  Rows=1000.0 (1000), root[root]@localhost</span><br><span class="line">  select * from film</span><br><span class="line"></span><br><span class="line">Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=200.0 (200), root[root]@localhost</span><br><span class="line">  select * from actor</span><br><span class="line"></span><br><span class="line">Count: 2  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=1.0 (2), root[root]@localhost</span><br><span class="line">  select count(*) from film</span><br><span class="line"></span><br><span class="line">Count: 2  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=1.0 (2), root[root]@localhost</span><br><span class="line">  select count(*) from staff</span><br><span class="line"></span><br><span class="line">Died at /usr/bin/mysqldumpslow line 161, &lt;&gt; chunk 6.</span><br></pre></td></tr></table></figure>
<p>如上图两条就是分析的结果，每条结果都显示是执行时间，锁定时间，发送的行数，扫描的行数</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line">[root@mysql mysql]# tail -f /var/lib/mysql/mysql-slow.log</span><br><span class="line"># Time: 181026  0:41:46</span><br><span class="line"># User@Host: root[root] @ localhost []  Id:     3</span><br><span class="line"># Query_time: 0.000121  Lock_time: 0.000059 Rows_sent: 1  Rows_examined: 2</span><br><span class="line">SET timestamp=1540485706;</span><br><span class="line">select count(*) from staff;</span><br><span class="line"># Time: 181026  0:42:12</span><br><span class="line"># User@Host: root[root] @ localhost []  Id:     3</span><br><span class="line"># Query_time: 0.000634  Lock_time: 0.000069 Rows_sent: 200  Rows_examined: 200</span><br><span class="line">SET timestamp=1540485732;</span><br><span class="line">select * from actor;</span><br><span class="line"># Time: 181026  1:05:41</span><br><span class="line"># User@Host: root[root] @ localhost []  Id:     3</span><br><span class="line"># Query_time: 0.008336  Lock_time: 0.000101 Rows_sent: 1000  Rows_examined: 1000</span><br><span class="line">SET timestamp=1540487141;</span><br><span class="line">select * from film;</span><br></pre></td></tr></table></figure>
<p>这个工具是最常用的工具，通过安装mysql进行附带安装，但是该工具统计的结果比较少，对我们的优化锁表现的数据还是比较少。</p>
<h2 id="5、MySQL慢查日志分析工具-pt-query-digest"><a href="#5、MySQL慢查日志分析工具-pt-query-digest" class="headerlink" title="5、MySQL慢查日志分析工具(pt-query-digest)"></a>5、MySQL慢查日志分析工具(pt-query-digest)</h2><h3 id="1、介绍及作用"><a href="#1、介绍及作用" class="headerlink" title="1、介绍及作用"></a>1、介绍及作用</h3><pre><code>作为一名优秀的mysql dba也需要有掌握几个好用的mysql管理工具，所以我也一直在整理和查找一些能够便于管理mysql的利器。以后的一段时间内，将会花一大部分的精力去搜索这些工具。
</code></pre><p>性 能的管理一直都是摆在第一位的，dba的很多工作管理层都看不到也没有办法衡量价值，但是如果一个系统慢的跟蜗牛一样，dba通过监控调优把系统从崩溃边缘重新拉回到高铁时代。这种价值和触动应该是巨大的。（很多企业的领导认为系统跑不动了就需要换更快的CPU、更大的内存、更快的存储，而且这还不是少数，所以DBA的价值也一直体现不出来，薪水自然也就不会很高）</p>
<p>mysql 的日志是跟踪mysql性能瓶颈的最快和最直接的方式了，系统性能出现瓶颈的时候，首先要打开慢查询日志，进行跟踪；这段时间关于慢查询日志的管理和查看已经整理过两篇文章了，不经意间又发现了一个查看慢查询日志的工具：mk-query-digest，这个工具网上号称mysql dba必须掌握的十大工具之首。</p>
<h2 id="2、安装pt-query-digest工具"><a href="#2、安装pt-query-digest工具" class="headerlink" title="2、安装pt-query-digest工具"></a>2、安装pt-query-digest工具</h2><h3 id="1-1、快速安装（注：必须先要安装wget）"><a href="#1-1、快速安装（注：必须先要安装wget）" class="headerlink" title="1.1、快速安装（注：必须先要安装wget）"></a>1.1、快速安装（注：必须先要安装wget）</h3><h3 id="1-2、检查是否安装完成："><a href="#1-2、检查是否安装完成：" class="headerlink" title="1.2、检查是否安装完成："></a>1.2、检查是否安装完成：</h3><pre><code>命令行中输入：pt-summary

      显示如下图所示：说明安装成功！输入【[root@node03 mysql]# pt-query-digest --help】
</code></pre><h3 id="1-3、工具使用简介："><a href="#1-3、工具使用简介：" class="headerlink" title="1.3、工具使用简介："></a>1.3、工具使用简介：</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br></pre></td><td class="code"><pre><span class="line">[root@mysql ~]# pt-summary --help</span><br><span class="line">Usage: pt-summary</span><br><span class="line"></span><br><span class="line">For more information, &apos;man pt-summary&apos; or &apos;perldoc /usr/bin/pt-summary&apos;.</span><br><span class="line"></span><br><span class="line">Command line options:</span><br><span class="line"></span><br><span class="line">  --config          Read this comma-separated list of config files.</span><br><span class="line">  --help            Print help and exit.</span><br><span class="line">  --read-samples    Create a report from the files in this directory.</span><br><span class="line">  --save-samples    Save the collected data in this directory.</span><br><span class="line">  --sleep           How long to sleep when gathering samples from vmstat.</span><br><span class="line">  --summarize-mounts</span><br><span class="line">                    Report on mounted filesystems and disk usage.</span><br><span class="line">  --summarize-network</span><br><span class="line">                    Report on network controllers and configuration.</span><br><span class="line">  --summarize-processes</span><br><span class="line">                    Report on top processes and C&lt;vmstat&gt; output.</span><br><span class="line">  --version         Print tool&apos;s version and exit.</span><br><span class="line"></span><br><span class="line">Options and values after processing arguments:</span><br><span class="line"></span><br><span class="line">  --config                         (No value)</span><br><span class="line">  --help                           TRUE</span><br><span class="line">  --read-samples                   (No value)</span><br><span class="line">  --save-samples                   (No value)</span><br><span class="line">  --sleep                          5</span><br><span class="line">  --summarize-mounts               TRUE</span><br><span class="line">  --summarize-network              TRUE</span><br><span class="line">  --summarize-processes            TRUE</span><br><span class="line">  --version                        FALSE</span><br><span class="line">[root@mysql ~]# pt-summary --version</span><br><span class="line">pt-summary 2.2.16</span><br><span class="line">[root@mysql ~]#</span><br></pre></td></tr></table></figure>
<h4 id="1、查看服务器信息"><a href="#1、查看服务器信息" class="headerlink" title="1、查看服务器信息"></a>1、查看服务器信息</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br><span class="line">70</span><br><span class="line">71</span><br><span class="line">72</span><br><span class="line">73</span><br><span class="line">74</span><br><span class="line">75</span><br><span class="line">76</span><br><span class="line">77</span><br><span class="line">78</span><br><span class="line">79</span><br><span class="line">80</span><br><span class="line">81</span><br><span class="line">82</span><br><span class="line">83</span><br><span class="line">84</span><br><span class="line">85</span><br><span class="line">86</span><br><span class="line">87</span><br><span class="line">88</span><br><span class="line">89</span><br><span class="line">90</span><br><span class="line">91</span><br><span class="line">92</span><br><span class="line">93</span><br><span class="line">94</span><br><span class="line">95</span><br><span class="line">96</span><br><span class="line">97</span><br><span class="line">98</span><br><span class="line">99</span><br><span class="line">100</span><br><span class="line">101</span><br><span class="line">102</span><br><span class="line">103</span><br><span class="line">104</span><br><span class="line">105</span><br><span class="line">106</span><br><span class="line">107</span><br><span class="line">108</span><br><span class="line">109</span><br><span class="line">110</span><br><span class="line">111</span><br><span class="line">112</span><br><span class="line">113</span><br><span class="line">114</span><br><span class="line">115</span><br><span class="line">116</span><br><span class="line">117</span><br><span class="line">118</span><br><span class="line">119</span><br><span class="line">120</span><br><span class="line">121</span><br><span class="line">122</span><br><span class="line">123</span><br><span class="line">124</span><br><span class="line">125</span><br><span class="line">126</span><br><span class="line">127</span><br><span class="line">128</span><br><span class="line">129</span><br><span class="line">130</span><br><span class="line">131</span><br><span class="line">132</span><br><span class="line">133</span><br><span class="line">134</span><br><span class="line">135</span><br><span class="line">136</span><br><span class="line">137</span><br><span class="line">138</span><br><span class="line">139</span><br><span class="line">140</span><br><span class="line">141</span><br><span class="line">142</span><br><span class="line">143</span><br><span class="line">144</span><br><span class="line">145</span><br><span class="line">146</span><br><span class="line">147</span><br><span class="line">148</span><br><span class="line">149</span><br><span class="line">150</span><br><span class="line">151</span><br><span class="line">152</span><br><span class="line">153</span><br><span class="line">154</span><br><span class="line">155</span><br><span class="line">156</span><br><span class="line">157</span><br><span class="line">158</span><br><span class="line">159</span><br><span class="line">160</span><br><span class="line">161</span><br><span class="line">162</span><br><span class="line">163</span><br><span class="line">164</span><br><span class="line">165</span><br><span class="line">166</span><br><span class="line">167</span><br><span class="line">168</span><br><span class="line">169</span><br><span class="line">170</span><br><span class="line">171</span><br><span class="line">172</span><br><span class="line">173</span><br><span class="line">174</span><br><span class="line">175</span><br><span class="line">176</span><br><span class="line">177</span><br><span class="line">178</span><br><span class="line">179</span><br><span class="line">180</span><br><span class="line">181</span><br><span class="line">182</span><br><span class="line">183</span><br><span class="line">184</span><br><span class="line">185</span><br><span class="line">186</span><br><span class="line">187</span><br><span class="line">188</span><br><span class="line">189</span><br><span class="line">190</span><br><span class="line">191</span><br><span class="line">192</span><br><span class="line">193</span><br><span class="line">194</span><br><span class="line">195</span><br><span class="line">196</span><br><span class="line">197</span><br><span class="line">198</span><br><span class="line">199</span><br><span class="line">200</span><br><span class="line">201</span><br><span class="line">202</span><br><span class="line">203</span><br><span class="line">204</span><br><span class="line">205</span><br><span class="line">206</span><br><span class="line">207</span><br><span class="line">208</span><br><span class="line">209</span><br><span class="line">210</span><br><span class="line">211</span><br><span class="line">212</span><br><span class="line">213</span><br><span class="line">214</span><br><span class="line">215</span><br><span class="line">216</span><br><span class="line">217</span><br><span class="line">218</span><br><span class="line">219</span><br><span class="line">220</span><br><span class="line">221</span><br><span class="line">222</span><br><span class="line">223</span><br><span class="line">224</span><br><span class="line">225</span><br><span class="line">226</span><br><span class="line">227</span><br><span class="line">228</span><br><span class="line">229</span><br><span class="line">230</span><br><span class="line">231</span><br><span class="line">232</span><br><span class="line">233</span><br><span class="line">234</span><br><span class="line">235</span><br><span class="line">236</span><br><span class="line">237</span><br><span class="line">238</span><br><span class="line">239</span><br><span class="line">240</span><br><span class="line">241</span><br><span class="line">242</span><br><span class="line">243</span><br><span class="line">244</span><br><span class="line">245</span><br><span class="line">246</span><br><span class="line">247</span><br><span class="line">248</span><br><span class="line">249</span><br><span class="line">250</span><br><span class="line">251</span><br><span class="line">252</span><br><span class="line">253</span><br><span class="line">254</span><br><span class="line">255</span><br><span class="line">256</span><br><span class="line">257</span><br><span class="line">258</span><br><span class="line">259</span><br><span class="line">260</span><br><span class="line">261</span><br><span class="line">262</span><br><span class="line">263</span><br><span class="line">264</span><br><span class="line">265</span><br><span class="line">266</span><br><span class="line">267</span><br><span class="line">268</span><br><span class="line">269</span><br><span class="line">270</span><br><span class="line">271</span><br><span class="line">272</span><br><span class="line">273</span><br><span class="line">274</span><br><span class="line">275</span><br><span class="line">276</span><br><span class="line">277</span><br><span class="line">278</span><br><span class="line">279</span><br><span class="line">280</span><br><span class="line">281</span><br><span class="line">282</span><br><span class="line">283</span><br><span class="line">284</span><br><span class="line">285</span><br><span class="line">286</span><br><span class="line">287</span><br><span class="line">288</span><br><span class="line">289</span><br><span class="line">290</span><br><span class="line">291</span><br><span class="line">292</span><br><span class="line">293</span><br><span class="line">294</span><br><span class="line">295</span><br><span class="line">296</span><br><span class="line">297</span><br><span class="line">298</span><br><span class="line">299</span><br><span class="line">300</span><br><span class="line">301</span><br><span class="line">302</span><br><span class="line">303</span><br><span class="line">304</span><br><span class="line">305</span><br><span class="line">306</span><br><span class="line">307</span><br><span class="line">308</span><br><span class="line">309</span><br><span class="line">310</span><br><span class="line">311</span><br><span class="line">312</span><br><span class="line">313</span><br><span class="line">314</span><br><span class="line">315</span><br><span class="line">316</span><br><span class="line">317</span><br><span class="line">318</span><br><span class="line">319</span><br><span class="line">320</span><br><span class="line">321</span><br><span class="line">322</span><br><span class="line">323</span><br><span class="line">324</span><br><span class="line">325</span><br><span class="line">326</span><br></pre></td><td class="code"><pre><span class="line">[root@mysql ~]# man pt-summary</span><br><span class="line">PT-SUMMARY(1p)        User Contributed Perl Documentation       PT-SUMMARY(1p)</span><br><span class="line"></span><br><span class="line">NAME</span><br><span class="line">       pt-summary - Summarize system information nicely.</span><br><span class="line"></span><br><span class="line">SYNOPSIS</span><br><span class="line">       Usage: pt-summary</span><br><span class="line"></span><br><span class="line">       pt-summary conveniently summarizes the status and configuration of a server.  It is not a tuning tool</span><br><span class="line">       or diagnosis tool.  It produces a report that is easy to diff and can be pasted into emails without</span><br><span class="line">       losing the formatting.  This tool works well on many types of Unix systems.</span><br><span class="line"></span><br><span class="line">       Download and run:</span><br><span class="line"></span><br><span class="line">          wget http://percona.com/get/pt-summary</span><br><span class="line">          bash ./pt-summary</span><br><span class="line"></span><br><span class="line">RISKS</span><br><span class="line">       Percona Toolkit is mature, proven in the real world, and well tested, but all database tools can pose</span><br><span class="line">       a risk to the system and the database server.  Before using this tool, please:</span><br><span class="line"></span><br><span class="line">       Â·   Read the toolâ€™s documentation</span><br><span class="line"></span><br><span class="line">       Â·   Review the toolâ€™s known &quot;BUGS&quot;</span><br><span class="line"></span><br><span class="line">       Â·   Test the tool on a non-production server</span><br><span class="line"></span><br><span class="line">       Â·   Backup your production server and verify the backups</span><br><span class="line"></span><br><span class="line">DESCRIPTION</span><br><span class="line">       pt-summary runs a large variety of commands to inspect system status and configuration, saves the</span><br><span class="line">       output into files in a temporary directory, and then runs Unix commands on these results to format</span><br><span class="line">       them nicely.  It works best when executed as a privileged user, but will also work without</span><br><span class="line">       privileges, although some output might not be possible to generate without root.</span><br><span class="line"></span><br><span class="line">OUTPUT</span><br><span class="line">       Many of the outputs from this tool are deliberately rounded to show their magnitude but not the exact</span><br><span class="line">       detail. This is called fuzzy-rounding. The idea is that it doesnâ€™t matter whether a particular</span><br><span class="line">       counter is 918 or 921; such a small variation is insignificant, and only makes the output hard to</span><br><span class="line">       compare to other servers. Fuzzy-rounding rounds in larger increments as the input grows. It begins by</span><br><span class="line">       rounding to the nearest 5, then the nearest 10, nearest 25, and then repeats by a factor of 10 larger</span><br><span class="line">       (50, 100, 250), and so on, as the input grows.</span><br><span class="line"></span><br><span class="line">       The following is a simple report generated from a CentOS virtual machine, broken into sections with</span><br><span class="line">       commentary following each section. Some long lines are reformatted for clarity when reading this</span><br><span class="line">       documentation as a manual page in a terminal.</span><br><span class="line"></span><br><span class="line">        # Percona Toolkit System Summary Report ######################</span><br><span class="line">                Date | 2012-03-30 00:58:07 UTC (local TZ: EDT -0400)</span><br><span class="line">            Hostname | localhost.localdomain</span><br><span class="line">              Uptime | 20:58:06 up 1 day, 20 min, 1 user,</span><br><span class="line">                       load average: 0.14, 0.18, 0.18</span><br><span class="line">              System | innotek GmbH; VirtualBox; v1.2 ()</span><br><span class="line">         Service Tag | 0</span><br><span class="line">            Platform | Linux</span><br><span class="line">             Release | CentOS release 5.5 (Final)</span><br><span class="line">              Kernel | 2.6.18-194.el5</span><br><span class="line">        Architecture | CPU = 32-bit, OS = 32-bit</span><br><span class="line">           Threading | NPTL 2.5</span><br><span class="line">            Compiler | GNU CC version 4.1.2 20080704 (Red Hat 4.1.2-48).</span><br><span class="line">             SELinux | Enforcing</span><br><span class="line">         Virtualized | VirtualBox</span><br><span class="line"></span><br><span class="line">       This section shows the current date and time, and a synopsis of the server and operating system.</span><br><span class="line"></span><br><span class="line">        # Processor ##################################################</span><br><span class="line">          Processors | physical = 1, cores = 0, virtual = 1, hyperthreading = no</span><br><span class="line">              Speeds | 1x2510.626</span><br><span class="line">              Models | 1xIntel(R) Core(TM) i5-2400S CPU @ 2.50GHz</span><br><span class="line">              Caches | 1x6144 KB</span><br><span class="line"></span><br><span class="line">       This section is derived from /proc/cpuinfo.</span><br><span class="line"></span><br><span class="line">        # Memory #####################################################</span><br><span class="line">               Total | 503.2M</span><br><span class="line">                Free | 29.0M</span><br><span class="line">                Used | physical = 474.2M, swap allocated = 1.0M,</span><br><span class="line">                       swap used = 16.0k, virtual = 474.3M</span><br><span class="line">             Buffers | 33.9M</span><br><span class="line">              Caches | 262.6M</span><br><span class="line">               Dirty | 396 kB</span><br><span class="line">             UsedRSS | 201.9M</span><br><span class="line">          Swappiness | 60</span><br><span class="line">         DirtyPolicy | 40, 10</span><br><span class="line">         Locator  Size  Speed    Form Factor  Type    Type Detail</span><br><span class="line">         =======  ====  =====    ===========  ====    ===========</span><br><span class="line"></span><br><span class="line">       Information about memory is gathered from &quot;free&quot;. The Used statistic is the total of the rss sizes</span><br><span class="line">       displayed by &quot;ps&quot;. The Dirty statistic for the cached value comes from /proc/meminfo. On Linux, the</span><br><span class="line">       swappiness settings are gathered from &quot;sysctl&quot;. The final portion of this section is a table of the</span><br><span class="line">       DIMMs, which comes from &quot;dmidecode&quot;. In this example there is no output.</span><br><span class="line"></span><br><span class="line">        # Mounted Filesystems ########################################</span><br><span class="line">          Filesystem                       Size Used Type  Opts Mountpoint</span><br><span class="line">          /dev/mapper/VolGroup00-LogVol00   15G  17% ext3  rw   /</span><br><span class="line">          /dev/sda1                         99M  13% ext3  rw   /boot</span><br><span class="line">          tmpfs                            252M   0% tmpfs rw   /dev/shm</span><br><span class="line"></span><br><span class="line">       The mounted filesystem section is a combination of information from &quot;mount&quot; and &quot;df&quot;. This section is</span><br><span class="line">       skipped if you disable &quot;--summarize-mounts&quot;.</span><br><span class="line"></span><br><span class="line">        # Disk Schedulers And Queue Size #############################</span><br><span class="line">                dm-0 | UNREADABLE</span><br><span class="line">                dm-1 | UNREADABLE</span><br><span class="line">                 hdc | [cfq] 128</span><br><span class="line">                 md0 | UNREADABLE</span><br><span class="line">                 sda | [cfq] 128</span><br><span class="line"></span><br><span class="line">       The disk scheduler information is extracted from the /sys filesystem in Linux.</span><br><span class="line"></span><br><span class="line">        # Disk Partioning ############################################</span><br><span class="line">        Device       Type      Start        End               Size</span><br><span class="line">        ============ ==== ========== ========== ==================</span><br><span class="line">        /dev/sda     Disk                              17179869184</span><br><span class="line">        /dev/sda1    Part          1         13           98703360</span><br><span class="line">        /dev/sda2    Part         14       2088        17059230720</span><br><span class="line"></span><br><span class="line">       Information about disk partitioning comes from &quot;fdisk -l&quot;.</span><br><span class="line"></span><br><span class="line">        # Kernel Inode State #########################################</span><br><span class="line">        dentry-state | 10697 8559  45 0  0  0</span><br><span class="line">             file-nr | 960   0  50539</span><br><span class="line">            inode-nr | 14059 8139</span><br><span class="line"></span><br><span class="line">       These lines are from the files of the same name in the /proc/sys/fs directory on Linux. Read the</span><br><span class="line">       &quot;proc&quot; man page to learn about the meaning of these files on your system.</span><br><span class="line"></span><br><span class="line">        # LVM Volumes ################################################</span><br><span class="line">        LV       VG         Attr   LSize   Origin Snap% Move Log Copy% Convert</span><br><span class="line">        LogVol00 VolGroup00 -wi-ao 269.00G</span><br><span class="line">        LogVol01 VolGroup00 -wi-ao   9.75G</span><br><span class="line"></span><br><span class="line">       This section shows the output of &quot;lvs&quot;.</span><br><span class="line"></span><br><span class="line">        # RAID Controller ############################################</span><br><span class="line">          Controller | No RAID controller detected</span><br><span class="line"></span><br><span class="line">       The tool can detect a variety of RAID controllers by examining &quot;lspci&quot; and &quot;dmesg&quot; information. If</span><br><span class="line">       the controller software is installed on the system, in many cases it is able to execute status</span><br><span class="line">       commands and show a summary of the RAID controllerâ€™s status and configuration. If your system is not</span><br><span class="line">       supported, please file a bug report.</span><br><span class="line"></span><br><span class="line">        # Network Config #############################################</span><br><span class="line">          Controller | Intel Corporation 82540EM Gigabit Ethernet Controller</span><br><span class="line">         FIN Timeout | 60</span><br><span class="line">          Port Range | 61000</span><br><span class="line"></span><br><span class="line">       The network controllers attached to the system are detected from &quot;lspci&quot;. The TCP/IP protocol</span><br><span class="line">       configuration parameters are extracted from &quot;sysctl&quot;. You can skip this section by disabling the</span><br><span class="line">       &quot;--summarize-network&quot; option.</span><br><span class="line"></span><br><span class="line">        # Interface Statistics #######################################</span><br><span class="line">        interface rx_bytes rx_packets rx_errors tx_bytes tx_packets tx_errors</span><br><span class="line">        ========= ======== ========== ========= ======== ========== =========</span><br><span class="line">        lo        60000000      12500         0 60000000      12500         0</span><br><span class="line">        eth0      15000000      80000         0  1500000      10000         0</span><br><span class="line">        sit0             0          0         0        0          0         0</span><br><span class="line"></span><br><span class="line">       Interface statistics are gathered from &quot;ip -s link&quot; and are fuzzy-rounded. The columns are received</span><br><span class="line">       and transmitted bytes, packets, and errors.  You can skip this section by disabling the</span><br><span class="line">       &quot;--summarize-network&quot; option.</span><br><span class="line"></span><br><span class="line">        # Network Connections ########################################</span><br><span class="line">          Connections from remote IP addresses</span><br><span class="line">            127.0.0.1           2</span><br><span class="line">          Connections to local IP addresses</span><br><span class="line">            127.0.0.1           2</span><br><span class="line">          Connections to top 10 local ports</span><br><span class="line">            38346               1</span><br><span class="line">            60875               1</span><br><span class="line">          States of connections</span><br><span class="line">            ESTABLISHED         5</span><br><span class="line">            LISTEN              8</span><br><span class="line"></span><br><span class="line">       This section shows a summary of network connections, retrieved from &quot;netstat&quot; and &quot;fuzzy-rounded&quot; to</span><br><span class="line">       make them easier to compare when the numbers grow large.  There are two sub-sections showing how many</span><br><span class="line">       connections there are per origin and destination IP address, and a sub-section showing the count of</span><br><span class="line">       ports in use.  The section ends with the count of the network connectionsâ€™ states.  You can skip this</span><br><span class="line">       section by disabling the &quot;--summarize-network&quot; option.</span><br><span class="line"></span><br><span class="line">        # Top Processes ##############################################</span><br><span class="line">          PID USER  PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND</span><br><span class="line">            1 root  15   0  2072  628  540 S  0.0  0.1   0:02.55 init</span><br><span class="line">            2 root  RT  -5     0    0    0 S  0.0  0.0   0:00.00 migration/0</span><br><span class="line">            3 root  34  19     0    0    0 S  0.0  0.0   0:00.03 ksoftirqd/0</span><br><span class="line">            4 root  RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/0</span><br><span class="line">            5 root  10  -5     0    0    0 S  0.0  0.0   0:00.97 events/0</span><br><span class="line">            6 root  10  -5     0    0    0 S  0.0  0.0   0:00.00 khelper</span><br><span class="line">            7 root  10  -5     0    0    0 S  0.0  0.0   0:00.00 kthread</span><br><span class="line">           10 root  10  -5     0    0    0 S  0.0  0.0   0:00.13 kblockd/0</span><br><span class="line">           11 root  20  -5     0    0    0 S  0.0  0.0   0:00.00 kacpid</span><br><span class="line">        # Notable Processes ##########################################</span><br><span class="line">          PID    OOM    COMMAND</span><br><span class="line">         2028    +0    sshd</span><br><span class="line"></span><br><span class="line">       This section shows the first few lines of &quot;top&quot; so that you can see what processes are actively using</span><br><span class="line">       CPU time.  The notable processes include the SSH daemon and any process whose out-of-memory-killer</span><br><span class="line">       priority is set to 17. You can skip this section by disabling the &quot;--summarize-processes&quot; option.</span><br><span class="line"></span><br><span class="line">        # Simplified and fuzzy rounded vmstat (wait please) ##########</span><br><span class="line">          procs  ---swap-- -----io---- ---system---- --------cpu--------</span><br><span class="line">           r  b    si   so    bi    bo     ir     cs  us  sy  il  wa  st</span><br><span class="line">           2  0     0    0     3    15     30    125   0   0  99   0   0</span><br><span class="line">           0  0     0    0     0     0   1250    800   6  10  84   0   0</span><br><span class="line">           0  0     0    0     0     0   1000    125   0   0 100   0   0</span><br><span class="line">           0  0     0    0     0     0   1000    125   0   0 100   0   0</span><br><span class="line">           0  0     0    0     0   450   1000    125   0   1  88  11   0</span><br><span class="line">        # The End ####################################################</span><br><span class="line"></span><br><span class="line">       This section is a trimmed-down sample of &quot;vmstat 1 5&quot;, so you can see the general status of the</span><br><span class="line">       system at present. The values in the table are fuzzy-rounded, except for the CPU columns.  You can</span><br><span class="line">       skip this section by disabling the &quot;--summarize-processes&quot; option.</span><br><span class="line"></span><br><span class="line">OPTIONS</span><br><span class="line">       --config</span><br><span class="line">           type: string</span><br><span class="line"></span><br><span class="line">           Read this comma-separated list of config files.  If specified, this must be the first option on</span><br><span class="line">           the command line.</span><br><span class="line"></span><br><span class="line">       --help</span><br><span class="line">           Print help and exit.</span><br><span class="line"></span><br><span class="line">       --read-samples</span><br><span class="line">           type: string</span><br><span class="line"></span><br><span class="line">           Create a report from the files in this directory.</span><br><span class="line"></span><br><span class="line">       --save-samples</span><br><span class="line">           type: string</span><br><span class="line"></span><br><span class="line">           Save the collected data in this directory.</span><br><span class="line"></span><br><span class="line">       --sleep</span><br><span class="line">           type: int; default: 5</span><br><span class="line"></span><br><span class="line">           How long to sleep when gathering samples from vmstat.</span><br><span class="line"></span><br><span class="line">       --summarize-mounts</span><br><span class="line">           default: yes; negatable: yes</span><br><span class="line"></span><br><span class="line">           Report on mounted filesystems and disk usage.</span><br><span class="line"></span><br><span class="line">       --summarize-network</span><br><span class="line">           default: yes; negatable: yes</span><br><span class="line"></span><br><span class="line">           Report on network controllers and configuration.</span><br><span class="line"></span><br><span class="line">       --summarize-processes</span><br><span class="line">           default: yes; negatable: yes</span><br><span class="line"></span><br><span class="line">           Report on top processes and &quot;vmstat&quot; output.</span><br><span class="line"></span><br><span class="line">       --version</span><br><span class="line">           Print toolâ€™s version and exit.</span><br><span class="line"></span><br><span class="line">ENVIRONMENT</span><br><span class="line">       This tool does not use any environment variables.</span><br><span class="line"></span><br><span class="line">SYSTEM REQUIREMENTS</span><br><span class="line">       This tool requires the Bourne shell (/bin/sh).</span><br><span class="line"></span><br><span class="line">BUGS</span><br><span class="line">       For a list of known bugs, see http://www.percona.com/bugs/pt-summary &lt;http://www.percona.com/bugs/pt-</span><br><span class="line">       summary&gt;.</span><br><span class="line"></span><br><span class="line">       Please report bugs at https://bugs.launchpad.net/percona-toolkit &lt;https://bugs.launchpad.net/percona-</span><br><span class="line">       toolkit&gt;.  Include the following information in your bug report:</span><br><span class="line"></span><br><span class="line">       Â·   Complete command-line used to run the tool</span><br><span class="line"></span><br><span class="line">       Â·   Tool &quot;--version&quot;</span><br><span class="line"></span><br><span class="line">       Â·   MySQL version of all servers involved</span><br><span class="line"></span><br><span class="line">       Â·   Output from the tool including STDERR</span><br><span class="line"></span><br><span class="line">       Â·   Input files (log/dump/config files, etc.)</span><br><span class="line"></span><br><span class="line">       If possible, include debugging output by running the tool with &quot;PTDEBUG&quot;; see &quot;ENVIRONMENT&quot;.</span><br><span class="line"></span><br><span class="line">DOWNLOADING</span><br><span class="line">       Visit http://www.percona.com/software/percona-toolkit/ &lt;http://www.percona.com/software/percona-</span><br><span class="line">       toolkit/&gt; to download the latest release of Percona Toolkit.  Or, get the latest release from the</span><br><span class="line">       command line:</span><br><span class="line"></span><br><span class="line">          wget percona.com/get/percona-toolkit.tar.gz</span><br><span class="line"></span><br><span class="line">          wget percona.com/get/percona-toolkit.rpm</span><br><span class="line"></span><br><span class="line">          wget percona.com/get/percona-toolkit.deb</span><br><span class="line"></span><br><span class="line">       You can also get individual tools from the latest release:</span><br><span class="line"></span><br><span class="line">          wget percona.com/get/TOOL</span><br><span class="line"></span><br><span class="line">       Replace &quot;TOOL&quot; with the name of any tool.</span><br><span class="line"></span><br><span class="line">AUTHORS</span><br><span class="line">       Baron Schwartz, Kevin van Zonneveld, and Brian Fraser</span><br><span class="line"></span><br><span class="line">ABOUT PERCONA TOOLKIT</span><br><span class="line">       This tool is part of Percona Toolkit, a collection of advanced command-line tools for MySQL developed</span><br><span class="line">       by Percona.  Percona Toolkit was forked from two projects in June, 2011: Maatkit and Aspersa.  Those</span><br><span class="line">       projects were created by Baron Schwartz and primarily developed by him and Daniel Nichter.  Visit</span><br><span class="line">       &lt;http://www.percona.com/software/&gt; to learn about other free, open-source software from Percona.</span><br><span class="line"></span><br><span class="line">COPYRIGHT, LICENSE, AND WARRANTY</span><br><span class="line">       This program is copyright 2011-2015 Percona LLC and/or its affiliates, 2010-2011 Baron Schwartz.</span><br><span class="line"></span><br><span class="line">       THIS PROGRAM IS PROVIDED &quot;AS IS&quot; AND WITHOUT ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, WITHOUT</span><br><span class="line">       LIMITATION, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.</span><br><span class="line"></span><br><span class="line">       This program is free software; you can redistribute it and/or modify it under the terms of the GNU</span><br><span class="line">       General Public License as published by the Free Software Foundation, version 2; OR the Perl Artistic</span><br><span class="line">       License.  On UNIX and similar systems, you can issue â€˜man perlgplâ€™ or â€˜man perlartisticâ€™ to read</span><br><span class="line">       these licenses.</span><br><span class="line"></span><br><span class="line">       You should have received a copy of the GNU General Public License along with this program; if not,</span><br><span class="line">       write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA.</span><br><span class="line"></span><br><span class="line">VERSION</span><br><span class="line">       pt-summary 2.2.16</span><br><span class="line"></span><br><span class="line">perl v5.14.2                      2015-11-06                    PT-SUMMARY(1p)</span><br></pre></td></tr></table></figure>
<h4 id="2、查看磁盘开销使用信息"><a href="#2、查看磁盘开销使用信息" class="headerlink" title="2、查看磁盘开销使用信息"></a>2、查看磁盘开销使用信息</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line">[root@mysql ~]# pt-diskstats</span><br><span class="line">  #ts device    rd_s rd_avkb rd_mb_s rd_mrg rd_cnc   rd_rt    wr_s wr_avkb wr_mb_s wr_mrg wr_cnc   wr_rt busy in_prg    io_s  qtime stime</span><br><span class="line">  1.0 sda        0.0     0.0     0.0     0%    0.0     0.0     5.0     4.0     0.0     0%    0.0     4.8   0%      0     5.0    3.8   1.0</span><br><span class="line">  1.0 sda2       0.0     0.0     0.0     0%    0.0     0.0     5.0     4.0     0.0     0%    0.0     4.8   0%      0     5.0    3.8   1.0</span><br><span class="line">  1.0 dm-0       0.0     0.0     0.0     0%    0.0     0.0     5.0     4.0     0.0     0%    0.0     4.8   0%      0     5.0    3.8   1.0</span><br><span class="line"></span><br><span class="line">  1.0 sda        0.0     0.0     0.0     0%    0.0     0.0     0.0     0.0     0.0     0%    0.0     0.0   0%      0     0.0    0.0   0.0</span><br><span class="line">  1.0 sda2       0.0     0.0     0.0     0%    0.0     0.0     0.0     0.0     0.0     0%    0.0     0.0   0%      0     0.0    0.0   0.0</span><br><span class="line">  1.0 dm-0       0.0     0.0     0.0     0%    0.0     0.0     0.0     0.0     0.0     0%    0.0     0.0   0%      0     0.0    0.0   0.0</span><br><span class="line"></span><br><span class="line">  1.0 sda        0.0     0.0     0.0     0%    0.0     0.0     0.0     0.0     0.0     0%    0.0     0.0   0%      0     0.0    0.0   0.0</span><br><span class="line">  1.0 sda2       0.0     0.0     0.0     0%    0.0     0.0     0.0     0.0     0.0     0%    0.0     0.0   0%      0     0.0    0.0   0.0</span><br><span class="line">  1.0 dm-0       0.0     0.0     0.0     0%    0.0     0.0     0.0     0.0     0.0     0%    0.0     0.0   0%      0     0.0    0.0   0.0</span><br></pre></td></tr></table></figure>
<h4 id="3、查看mysql数据库信息"><a href="#3、查看mysql数据库信息" class="headerlink" title="3、查看mysql数据库信息"></a>3、查看mysql数据库信息</h4><p><img src="file:///C:/Users/Deborah/AppData/Local/Temp/msohtmlclip1/01/clip_image018.png" alt=""></p>
<h4 id="4、分析慢查询日志"><a href="#4、分析慢查询日志" class="headerlink" title="4、分析慢查询日志"></a>4、分析慢查询日志</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br><span class="line">70</span><br><span class="line">71</span><br><span class="line">72</span><br><span class="line">73</span><br><span class="line">74</span><br><span class="line">75</span><br><span class="line">76</span><br><span class="line">77</span><br><span class="line">78</span><br><span class="line">79</span><br><span class="line">80</span><br><span class="line">81</span><br><span class="line">82</span><br><span class="line">83</span><br><span class="line">84</span><br><span class="line">85</span><br><span class="line">86</span><br><span class="line">87</span><br><span class="line">88</span><br><span class="line">89</span><br><span class="line">90</span><br><span class="line">91</span><br><span class="line">92</span><br><span class="line">93</span><br><span class="line">94</span><br><span class="line">95</span><br><span class="line">96</span><br><span class="line">97</span><br><span class="line">98</span><br><span class="line">99</span><br><span class="line">100</span><br><span class="line">101</span><br><span class="line">102</span><br><span class="line">103</span><br><span class="line">104</span><br><span class="line">105</span><br><span class="line">106</span><br><span class="line">107</span><br><span class="line">108</span><br><span class="line">109</span><br><span class="line">110</span><br><span class="line">111</span><br><span class="line">112</span><br><span class="line">113</span><br><span class="line">114</span><br><span class="line">115</span><br><span class="line">116</span><br><span class="line">117</span><br><span class="line">118</span><br><span class="line">119</span><br><span class="line">120</span><br><span class="line">121</span><br><span class="line">122</span><br><span class="line">123</span><br><span class="line">124</span><br><span class="line">125</span><br><span class="line">126</span><br><span class="line">127</span><br><span class="line">128</span><br><span class="line">129</span><br><span class="line">130</span><br><span class="line">131</span><br><span class="line">132</span><br><span class="line">133</span><br><span class="line">134</span><br><span class="line">135</span><br><span class="line">136</span><br><span class="line">137</span><br><span class="line">138</span><br><span class="line">139</span><br><span class="line">140</span><br><span class="line">141</span><br><span class="line">142</span><br><span class="line">143</span><br><span class="line">144</span><br><span class="line">145</span><br><span class="line">146</span><br><span class="line">147</span><br><span class="line">148</span><br><span class="line">149</span><br></pre></td><td class="code"><pre><span class="line">[root@mysql ~]# pt-query-digest /var/lib/mysql/mysql-slow.log </span><br><span class="line"></span><br><span class="line"># 410ms user time, 110ms system time, 24.40M rss, 204.76M vsz</span><br><span class="line"># Current date: Fri Oct 26 02:02:37 2018</span><br><span class="line"># Hostname: mysql</span><br><span class="line"># Files: /var/lib/mysql/mysql-slow.log</span><br><span class="line"># Overall: 8 total, 6 unique, 0.00 QPS, 0.00x concurrency ________________</span><br><span class="line"># Time range: 2018-10-26 00:36:02 to 01:50:11</span><br><span class="line"># Attribute          total     min     max     avg     95%  stddev  median</span><br><span class="line"># ============     ======= ======= ======= ======= ======= ======= =======</span><br><span class="line"># Exec time           27ms    98us    15ms     3ms    15ms     5ms     1ms</span><br><span class="line"># Lock time            2ms    50us     2ms   293us     2ms   471us   105us</span><br><span class="line"># Rows sent          1.18k       1    1000  150.75  964.41  315.86    0.99</span><br><span class="line"># Rows examine       3.14k       2    1000  401.88  964.41  450.80  578.59</span><br><span class="line"># Query size           279      18      92   34.88   88.31   22.24   24.84</span><br><span class="line"></span><br><span class="line"># Profile</span><br><span class="line"># Rank Query ID           Response time Calls R/Call V/M   Item</span><br><span class="line"># ==== ================== ============= ===== ====== ===== ===============</span><br><span class="line">#    1 0x3A23B0CB7839AF05  0.0153 55.9%     1 0.0153  0.00 SELECT INFORMATION_SCHEMA.TRIGGERS</span><br><span class="line">#    2 0x687D590364E29465  0.0083 30.5%     1 0.0083  0.00 SELECT film</span><br><span class="line">#    3 0x9134F278CE0AB549  0.0017  6.2%     1 0.0017  0.00 SELECT mysql.user</span><br><span class="line">#    4 0xEBA2FBA69B1FF476  0.0012  4.2%     2 0.0006  0.00 SELECT film</span><br><span class="line"># MISC 0xMISC              0.0009  3.1%     3 0.0003   0.0 &lt;2 ITEMS&gt;</span><br><span class="line"></span><br><span class="line"># Query 1: 0 QPS, 0x concurrency, ID 0x3A23B0CB7839AF05 at byte 1678 _____</span><br><span class="line"># This item is included in the report because it matches --limit.</span><br><span class="line"># Scores: V/M = 0.00</span><br><span class="line"># Time range: all events occurred at 2018-10-26 01:50:11</span><br><span class="line"># Attribute    pct   total     min     max     avg     95%  stddev  median</span><br><span class="line"># ============ === ======= ======= ======= ======= ======= ======= =======</span><br><span class="line"># Count         12       1</span><br><span class="line"># Exec time     55    15ms    15ms    15ms    15ms    15ms       0    15ms</span><br><span class="line"># Lock time     16   387us   387us   387us   387us   387us       0   387us</span><br><span class="line"># Rows sent      0       1       1       1       1       1       0       1</span><br><span class="line"># Rows examine   0       6       6       6       6       6       0       6</span><br><span class="line"># Query size    17      48      48      48      48      48       0      48</span><br><span class="line"># String:</span><br><span class="line"># Databases    sakila</span><br><span class="line"># Hosts        localhost</span><br><span class="line"># Users        root</span><br><span class="line"># Query_time distribution</span><br><span class="line">#   1us</span><br><span class="line">#  10us</span><br><span class="line"># 100us</span><br><span class="line">#   1ms</span><br><span class="line">#  10ms  ################################################################</span><br><span class="line"># 100ms</span><br><span class="line">#    1s</span><br><span class="line">#  10s+</span><br><span class="line"># Tables</span><br><span class="line">#    SHOW TABLE STATUS FROM `INFORMATION_SCHEMA` LIKE &apos;TRIGGERS&apos;\G</span><br><span class="line">#    SHOW CREATE TABLE `INFORMATION_SCHEMA`.`TRIGGERS`\G</span><br><span class="line"># EXPLAIN /*!50100 PARTITIONS*/</span><br><span class="line">SELECT COUNT(*) FROM INFORMATION_SCHEMA.TRIGGERS\G</span><br><span class="line"></span><br><span class="line"># Query 2: 0 QPS, 0x concurrency, ID 0x687D590364E29465 at byte 1208 _____</span><br><span class="line"># This item is included in the report because it matches --limit.</span><br><span class="line"># Scores: V/M = 0.00</span><br><span class="line"># Time range: all events occurred at 2018-10-26 01:05:41</span><br><span class="line"># Attribute    pct   total     min     max     avg     95%  stddev  median</span><br><span class="line"># ============ === ======= ======= ======= ======= ======= ======= =======</span><br><span class="line"># Count         12       1</span><br><span class="line"># Exec time     30     8ms     8ms     8ms     8ms     8ms       0     8ms</span><br><span class="line"># Lock time      4   101us   101us   101us   101us   101us       0   101us</span><br><span class="line"># Rows sent     82    1000    1000    1000    1000    1000       0    1000</span><br><span class="line"># Rows examine  31    1000    1000    1000    1000    1000       0    1000</span><br><span class="line"># Query size     6      18      18      18      18      18       0      18</span><br><span class="line"># String:</span><br><span class="line"># Databases    sakila</span><br><span class="line"># Hosts        localhost</span><br><span class="line"># Users        root</span><br><span class="line"># Query_time distribution</span><br><span class="line">#   1us</span><br><span class="line">#  10us</span><br><span class="line"># 100us</span><br><span class="line">#   1ms  ################################################################</span><br><span class="line">#  10ms</span><br><span class="line"># 100ms</span><br><span class="line">#    1s</span><br><span class="line">#  10s+</span><br><span class="line"># Tables</span><br><span class="line">#    SHOW TABLE STATUS FROM `sakila` LIKE &apos;film&apos;\G</span><br><span class="line">#    SHOW CREATE TABLE `sakila`.`film`\G</span><br><span class="line"># EXPLAIN /*!50100 PARTITIONS*/</span><br><span class="line">select * from film\G</span><br><span class="line"></span><br><span class="line"># Query 3: 0 QPS, 0x concurrency, ID 0x9134F278CE0AB549 at byte 1409 _____</span><br><span class="line"># This item is included in the report because it matches --limit.</span><br><span class="line"># Scores: V/M = 0.00</span><br><span class="line"># Time range: all events occurred at 2018-10-26 01:50:11</span><br><span class="line"># Attribute    pct   total     min     max     avg     95%  stddev  median</span><br><span class="line"># ============ === ======= ======= ======= ======= ======= ======= =======</span><br><span class="line"># Count         12       1</span><br><span class="line"># Exec time      6     2ms     2ms     2ms     2ms     2ms       0     2ms</span><br><span class="line"># Lock time     64     2ms     2ms     2ms     2ms     2ms       0     2ms</span><br><span class="line"># Rows sent      0       1       1       1       1       1       0       1</span><br><span class="line"># Rows examine   0       5       5       5       5       5       0       5</span><br><span class="line"># Query size    32      92      92      92      92      92       0      92</span><br><span class="line"># String:</span><br><span class="line"># Databases    sakila</span><br><span class="line"># Hosts        localhost</span><br><span class="line"># Users        root</span><br><span class="line"># Query_time distribution</span><br><span class="line">#   1us</span><br><span class="line">#  10us</span><br><span class="line"># 100us</span><br><span class="line">#   1ms  ################################################################</span><br><span class="line">#  10ms</span><br><span class="line"># 100ms</span><br><span class="line">#    1s</span><br><span class="line">#  10s+</span><br><span class="line"># Tables</span><br><span class="line">#    SHOW TABLE STATUS FROM `mysql` LIKE &apos;user&apos;\G</span><br><span class="line">#    SHOW CREATE TABLE `mysql`.`user`\G</span><br><span class="line"># EXPLAIN /*!50100 PARTITIONS*/</span><br><span class="line">SELECT COUNT(*), SUM(user=&quot;&quot;), SUM(password=&quot;&quot;), SUM(password NOT LIKE &quot;*%&quot;) FROM mysql.user\G</span><br><span class="line"></span><br><span class="line"># Query 4: 0.01 QPS, 0.00x concurrency, ID 0xEBA2FBA69B1FF476 at byte 0 __</span><br><span class="line"># This item is included in the report because it matches --limit.</span><br><span class="line"># Scores: V/M = 0.00</span><br><span class="line"># Time range: 2018-10-26 00:36:02 to 00:39:22</span><br><span class="line"># Attribute    pct   total     min     max     avg     95%  stddev  median</span><br><span class="line"># ============ === ======= ======= ======= ======= ======= ======= =======</span><br><span class="line"># Count         25       2</span><br><span class="line"># Exec time      4     1ms   413us   746us   579us   746us   235us   579us</span><br><span class="line"># Lock time      7   167us    51us   116us    83us   116us    45us    83us</span><br><span class="line"># Rows sent      0       2       1       1       1       1       0       1</span><br><span class="line"># Rows examine  62   1.95k    1000    1000    1000    1000       0    1000</span><br><span class="line"># Query size    17      50      25      25      25      25       0      25</span><br><span class="line"># String:</span><br><span class="line"># Databases    sakila</span><br><span class="line"># Hosts        localhost</span><br><span class="line"># Users        root</span><br><span class="line"># Query_time distribution</span><br><span class="line">#   1us</span><br><span class="line">#  10us</span><br><span class="line"># 100us  ################################################################</span><br><span class="line">#   1ms</span><br><span class="line">#  10ms</span><br><span class="line"># 100ms</span><br><span class="line">#    1s</span><br><span class="line">#  10s+</span><br><span class="line"># Tables</span><br><span class="line">#    SHOW TABLE STATUS FROM `sakila` LIKE &apos;film&apos;\G</span><br><span class="line">#    SHOW CREATE TABLE `sakila`.`film`\G</span><br><span class="line"># EXPLAIN /*!50100 PARTITIONS*/</span><br><span class="line">select count(*) from film\G</span><br><span class="line">[root@mysql ~]#</span><br></pre></td></tr></table></figure>
<h4 id="5、查找mysql的从库和同步状态"><a href="#5、查找mysql的从库和同步状态" class="headerlink" title="5、查找mysql的从库和同步状态"></a>5、查找mysql的从库和同步状态</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line">[root@mysql ~]# pt-slave-find --host=localhost --user=root --password=123456</span><br><span class="line">localhost</span><br><span class="line">Version         5.6.25</span><br><span class="line">Server ID       0</span><br><span class="line">Uptime          3+01:31:37 (started 2018-10-23T00:31:39)</span><br><span class="line">Replication     Is not a slave, has 0 slaves connected, is not read_only</span><br><span class="line">Filters         </span><br><span class="line">Binary logging  STATEMENT</span><br><span class="line">Slave status    </span><br><span class="line">Slave mode      STRICT</span><br><span class="line">Auto-increment  increment 1, offset 1</span><br><span class="line">InnoDB version  5.6.25</span><br><span class="line">[root@mysql ~]# ^C</span><br><span class="line">[root@mysql ~]#</span><br></pre></td></tr></table></figure>
<h4 id="6、查看mysql的死锁信息"><a href="#6、查看mysql的死锁信息" class="headerlink" title="6、查看mysql的死锁信息"></a>6、查看mysql的死锁信息</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">pt-deadlock-logger --user=root --password=123456 localhost</span><br></pre></td></tr></table></figure>
<h4 id="7、从慢查询日志中分析索引使用情况"><a href="#7、从慢查询日志中分析索引使用情况" class="headerlink" title="7、从慢查询日志中分析索引使用情况"></a>7、从慢查询日志中分析索引使用情况</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">[root@mysql ~]# pt-index-usage --user=root --password=123456 localhost /var/lib/mysql/mysql-slow.log </span><br><span class="line">localhost does not exist or is not readable at /usr/bin/pt-index-usage line 4447.</span><br><span class="line"></span><br><span class="line">ALTER TABLE `sakila`.`actor` DROP KEY `idx_actor_last_name`; -- type:non-unique</span><br><span class="line"></span><br><span class="line">ALTER TABLE `sakila`.`film` DROP KEY `idx_fk_original_language_id`, DROP KEY `idx_title`; -- type:non-unique</span><br><span class="line"></span><br><span class="line">ALTER TABLE `sakila`.`staff` DROP KEY `idx_fk_address_id`; -- type:non-unique</span><br></pre></td></tr></table></figure>
<h4 id="8、查找数据库表中重复的索引"><a href="#8、查找数据库表中重复的索引" class="headerlink" title="8、查找数据库表中重复的索引"></a>8、查找数据库表中重复的索引</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br></pre></td><td class="code"><pre><span class="line">[root@mysql ~]# pt-duplicate-key-checker --host=localhost --user=root --password=123456</span><br><span class="line"># ########################################################################</span><br><span class="line"># menagerie.A                                                             </span><br><span class="line"># ########################################################################</span><br><span class="line"></span><br><span class="line"># id is a duplicate of PRIMARY</span><br><span class="line"># Key definitions:</span><br><span class="line">#   KEY `id` (`A_ID`)</span><br><span class="line">#   PRIMARY KEY (`A_ID`),</span><br><span class="line"># Column types:</span><br><span class="line">#         `a_id` int(11) not null auto_increment</span><br><span class="line"># To remove this duplicate index, execute:</span><br><span class="line">ALTER TABLE `menagerie`.`A` DROP INDEX `id`;</span><br><span class="line"></span><br><span class="line"># ########################################################################</span><br><span class="line"># Summary of indexes                                                      </span><br><span class="line"># ########################################################################</span><br><span class="line"></span><br><span class="line"># Size Duplicate Indexes   12</span><br><span class="line"># Total Duplicate Indexes  1</span><br><span class="line"># Total Indexes            99</span><br><span class="line">[root@mysql ~]#</span><br></pre></td></tr></table></figure>
<h4 id="9、查看mysql表和文件的当前活动IO开销"><a href="#9、查看mysql表和文件的当前活动IO开销" class="headerlink" title="9、查看mysql表和文件的当前活动IO开销"></a>9、查看mysql表和文件的当前活动IO开销</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">[root@mysql ~]# pt-ioprofile</span><br><span class="line">Fri Oct 26 02:14:17 CST 2018</span><br><span class="line">Tracing process ID 37860</span><br><span class="line">     total filename</span><br><span class="line">[root@mysql ~]#</span><br></pre></td></tr></table></figure>
<h4 id="10、查看不同mysql配置文件的差异"><a href="#10、查看不同mysql配置文件的差异" class="headerlink" title="10、查看不同mysql配置文件的差异"></a>10、查看不同mysql配置文件的差异</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">[root@mysql ~]# pt-config-diff /etc/my.cnf /etc/my_master.cnf</span><br><span class="line">[root@mysql ~]#</span><br></pre></td></tr></table></figure>
<h4 id="11、pt-find查找mysql表和执行命令，示例如下"><a href="#11、pt-find查找mysql表和执行命令，示例如下" class="headerlink" title="11、pt-find查找mysql表和执行命令，示例如下"></a>11、pt-find查找mysql表和执行命令，示例如下</h4><p>查找数据库里大于2G的表：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">[root@mysql ~]# pt-find --user=root --password=123456 --tablesize +2G</span><br><span class="line">[root@mysql ~]#</span><br></pre></td></tr></table></figure>
<p>查找10天前创建，MyISAM引擎的表：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">[root@mysql ~]# pt-find --user=root --password=123456 --ctime +10 --engine MyISAM</span><br><span class="line">[root@mysql ~]#</span><br></pre></td></tr></table></figure>
<p>查看表和索引大小并排序</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br><span class="line">70</span><br><span class="line">71</span><br><span class="line">72</span><br><span class="line">73</span><br><span class="line">74</span><br><span class="line">75</span><br><span class="line">76</span><br><span class="line">77</span><br><span class="line">78</span><br><span class="line">79</span><br><span class="line">80</span><br><span class="line">81</span><br><span class="line">82</span><br><span class="line">83</span><br><span class="line">84</span><br><span class="line">85</span><br><span class="line">86</span><br><span class="line">87</span><br><span class="line">88</span><br><span class="line">89</span><br><span class="line">90</span><br><span class="line">91</span><br><span class="line">92</span><br><span class="line">93</span><br><span class="line">94</span><br><span class="line">95</span><br><span class="line">96</span><br><span class="line">97</span><br><span class="line">98</span><br><span class="line">99</span><br><span class="line">100</span><br><span class="line">101</span><br><span class="line">102</span><br><span class="line">103</span><br><span class="line">104</span><br><span class="line">105</span><br><span class="line">106</span><br><span class="line">107</span><br><span class="line">108</span><br><span class="line">109</span><br><span class="line">110</span><br><span class="line">111</span><br><span class="line">112</span><br><span class="line">113</span><br><span class="line">114</span><br><span class="line">115</span><br></pre></td><td class="code"><pre><span class="line">[root@mysql ~]# pt-find --user=root --password=123456 --printf &quot;%T\t%D.%N\n&quot; | sort -rn</span><br><span class="line">2785280 `sakila`.`rental`</span><br><span class="line">2228224 `sakila`.`payment`</span><br><span class="line">588216  `mysql`.`help_topic`</span><br><span class="line">376832  `sakila`.`inventory`</span><br><span class="line">278528  `sakila`.`film_actor`</span><br><span class="line">278528  `sakila`.`film`</span><br><span class="line">196608  `sakila`.`film_text`</span><br><span class="line">141280  `mysql`.`help_keyword`</span><br><span class="line">131072  `sakila`.`customer`</span><br><span class="line">98304   `sakila`.`staff`</span><br><span class="line">98304   `sakila`.`address`</span><br><span class="line">81920   `sakila`.`film_category`</span><br><span class="line">65536   `sakila`.`city`</span><br><span class="line">49152   `sakila`.`store`</span><br><span class="line">49152   `mysql`.`innodb_index_stats`</span><br><span class="line">33463   `mysql`.`help_relation`</span><br><span class="line">32768   `sakila`.`actor`</span><br><span class="line">16384   `sakila`.`language`</span><br><span class="line">16384   `sakila`.`country`</span><br><span class="line">16384   `sakila`.`category`</span><br><span class="line">16384   `mysql`.`slave_worker_info`</span><br><span class="line">16384   `mysql`.`slave_relay_log_info`</span><br><span class="line">16384   `mysql`.`slave_master_info`</span><br><span class="line">16384   `mysql`.`innodb_table_stats`</span><br><span class="line">16384   `menagerie`.`shop`</span><br><span class="line">16384   `menagerie`.`pet`</span><br><span class="line">16384   `menagerie`.`employee_tbl`</span><br><span class="line">16384   `menagerie`.`employee`</span><br><span class="line">16384   `menagerie`.`B`</span><br><span class="line">16384   `menagerie`.`A`</span><br><span class="line">16384   `test22`.`shop`</span><br><span class="line">16384   `test22`.`pet`</span><br><span class="line">16384   `test22`.`B`</span><br><span class="line">16384   `test22`.`A`</span><br><span class="line">12388   `mysql`.`proc`</span><br><span class="line">6506    `mysql`.`proxies_priv`</span><br><span class="line">6000    `mysql`.`db`</span><br><span class="line">4192    `mysql`.`help_category`</span><br><span class="line">4096    `mysql`.`tables_priv`</span><br><span class="line">4096    `mysql`.`procs_priv`</span><br><span class="line">4096    `mysql`.`columns_priv`</span><br><span class="line">2692    `mysql`.`user`</span><br><span class="line">2048    `mysql`.`event`</span><br><span class="line">1024    `mysql`.`time_zone_transition_type`</span><br><span class="line">1024    `mysql`.`time_zone_transition`</span><br><span class="line">1024    `mysql`.`time_zone_name`</span><br><span class="line">1024    `mysql`.`time_zone_leap_second`</span><br><span class="line">1024    `mysql`.`time_zone`</span><br><span class="line">1024    `mysql`.`servers`</span><br><span class="line">1024    `mysql`.`plugin`</span><br><span class="line">1024    `mysql`.`ndb_binlog_index`</span><br><span class="line">1024    `mysql`.`func`</span><br><span class="line">0       `sakila`.`staff_list`</span><br><span class="line">0       `sakila`.`sales_by_store`</span><br><span class="line">0       `sakila`.`sales_by_film_category`</span><br><span class="line">0       `sakila`.`nicer_but_slower_film_list`</span><br><span class="line">0       `sakila`.`film_list`</span><br><span class="line">0       `sakila`.`customer_list`</span><br><span class="line">0       `sakila`.`actor_info`</span><br><span class="line">0       `performance_schema`.`users`</span><br><span class="line">0       `performance_schema`.`threads`</span><br><span class="line">0       `performance_schema`.`table_lock_waits_summary_by_table`</span><br><span class="line">0       `performance_schema`.`table_io_waits_summary_by_table`</span><br><span class="line">0       `performance_schema`.`table_io_waits_summary_by_index_usage`</span><br><span class="line">0       `performance_schema`.`socket_summary_by_instance`</span><br><span class="line">0       `performance_schema`.`socket_summary_by_event_name`</span><br><span class="line">0       `performance_schema`.`socket_instances`</span><br><span class="line">0       `performance_schema`.`setup_timers`</span><br><span class="line">0       `performance_schema`.`setup_objects`</span><br><span class="line">0       `performance_schema`.`setup_instruments`</span><br><span class="line">0       `performance_schema`.`setup_consumers`</span><br><span class="line">0       `performance_schema`.`setup_actors`</span><br><span class="line">0       `performance_schema`.`session_connect_attrs`</span><br><span class="line">0       `performance_schema`.`session_account_connect_attrs`</span><br><span class="line">0       `performance_schema`.`rwlock_instances`</span><br><span class="line">0       `performance_schema`.`performance_timers`</span><br><span class="line">0       `performance_schema`.`objects_summary_global_by_type`</span><br><span class="line">0       `performance_schema`.`mutex_instances`</span><br><span class="line">0       `performance_schema`.`hosts`</span><br><span class="line">0       `performance_schema`.`host_cache`</span><br><span class="line">0       `performance_schema`.`file_summary_by_instance`</span><br><span class="line">0       `performance_schema`.`file_summary_by_event_name`</span><br><span class="line">0       `performance_schema`.`file_instances`</span><br><span class="line">0       `performance_schema`.`events_waits_summary_global_by_event_name`</span><br><span class="line">0       `performance_schema`.`events_waits_summary_by_user_by_event_name`</span><br><span class="line">0       `performance_schema`.`events_waits_summary_by_thread_by_event_name`</span><br><span class="line">0       `performance_schema`.`events_waits_summary_by_instance`</span><br><span class="line">0       `performance_schema`.`events_waits_summary_by_host_by_event_name`</span><br><span class="line">0       `performance_schema`.`events_waits_summary_by_account_by_event_name`</span><br><span class="line">0       `performance_schema`.`events_waits_history_long`</span><br><span class="line">0       `performance_schema`.`events_waits_history`</span><br><span class="line">0       `performance_schema`.`events_waits_current`</span><br><span class="line">0       `performance_schema`.`events_statements_summary_global_by_event_name`</span><br><span class="line">0       `performance_schema`.`events_statements_summary_by_user_by_event_name`</span><br><span class="line">0       `performance_schema`.`events_statements_summary_by_thread_by_event_name`</span><br><span class="line">0       `performance_schema`.`events_statements_summary_by_host_by_event_name`</span><br><span class="line">0       `performance_schema`.`events_statements_summary_by_digest`</span><br><span class="line">0       `performance_schema`.`events_statements_summary_by_account_by_event_name`</span><br><span class="line">0       `performance_schema`.`events_statements_history_long`</span><br><span class="line">0       `performance_schema`.`events_statements_history`</span><br><span class="line">0       `performance_schema`.`events_statements_current`</span><br><span class="line">0       `performance_schema`.`events_stages_summary_global_by_event_name`</span><br><span class="line">0       `performance_schema`.`events_stages_summary_by_user_by_event_name`</span><br><span class="line">0       `performance_schema`.`events_stages_summary_by_thread_by_event_name`</span><br><span class="line">0       `performance_schema`.`events_stages_summary_by_host_by_event_name`</span><br><span class="line">0       `performance_schema`.`events_stages_summary_by_account_by_event_name`</span><br><span class="line">0       `performance_schema`.`events_stages_history_long`</span><br><span class="line">0       `performance_schema`.`events_stages_history`</span><br><span class="line">0       `performance_schema`.`events_stages_current`</span><br><span class="line">0       `performance_schema`.`cond_instances`</span><br><span class="line">0       `performance_schema`.`accounts`</span><br><span class="line">0       `mysql`.`slow_log`</span><br><span class="line">0       `mysql`.`general_log`</span><br><span class="line">[root@mysql ~]#</span><br></pre></td></tr></table></figure>
<h4 id="12、pt-kill-杀掉符合标准的mysql进程"><a href="#12、pt-kill-杀掉符合标准的mysql进程" class="headerlink" title="12、pt-kill 杀掉符合标准的mysql进程"></a>12、pt-kill 杀掉符合标准的mysql进程</h4><p>显示查询时间大于60秒的查询</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">[root@mysql ~]# pt-kill --user=root --password=123456 --busy-time 60 --print</span><br><span class="line">^C</span><br><span class="line">[root@mysql ~]#</span><br></pre></td></tr></table></figure>
<p>kill掉大于60秒的查询</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">pt-kill --user=root --password=123456 --busy-time 60 --kill</span><br></pre></td></tr></table></figure>
<h4 id="13、查看mysql授权"><a href="#13、查看mysql授权" class="headerlink" title="13、查看mysql授权"></a>13、查看mysql授权</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br></pre></td><td class="code"><pre><span class="line">^C</span><br><span class="line">[root@mysql ~]# pt-show-grants --user=root --password=123456</span><br><span class="line">-- Grants dumped by pt-show-grants</span><br><span class="line">-- Dumped from server Localhost via UNIX socket, MySQL 5.6.25 at 2018-10-26 02:45:29</span><br><span class="line">-- Grants for &apos;root&apos;@&apos;%&apos;</span><br><span class="line">GRANT ALL PRIVILEGES ON *.* TO &apos;root&apos;@&apos;%&apos; IDENTIFIED BY PASSWORD &apos;*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9&apos;;</span><br><span class="line">-- Grants for &apos;root&apos;@&apos;127.0.0.1&apos;</span><br><span class="line">GRANT ALL PRIVILEGES ON *.* TO &apos;root&apos;@&apos;127.0.0.1&apos; IDENTIFIED BY PASSWORD &apos;*6E666163AC88D2C7122156EB3B633E3172F24604&apos; WITH GRANT OPTION;</span><br><span class="line">-- Grants for &apos;root&apos;@&apos;::1&apos;</span><br><span class="line">GRANT ALL PRIVILEGES ON *.* TO &apos;root&apos;@&apos;::1&apos; IDENTIFIED BY PASSWORD &apos;*6E666163AC88D2C7122156EB3B633E3172F24604&apos; WITH GRANT OPTION;</span><br><span class="line">-- Grants for &apos;root&apos;@&apos;localhost&apos;</span><br><span class="line">GRANT ALL PRIVILEGES ON *.* TO &apos;root&apos;@&apos;localhost&apos; IDENTIFIED BY PASSWORD &apos;*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9&apos; WITH GRANT OPTION;</span><br><span class="line">GRANT PROXY ON &apos;&apos;@&apos;&apos; TO &apos;root&apos;@&apos;localhost&apos; WITH GRANT OPTION;</span><br><span class="line">-- Grants for &apos;root&apos;@&apos;mysql&apos;</span><br><span class="line">GRANT ALL PRIVILEGES ON *.* TO &apos;root&apos;@&apos;mysql&apos; IDENTIFIED BY PASSWORD &apos;*6E666163AC88D2C7122156EB3B633E3172F24604&apos; WITH GRANT OPTION;</span><br><span class="line">GRANT PROXY ON &apos;&apos;@&apos;&apos; TO &apos;root&apos;@&apos;mysql&apos; WITH GRANT OPTION;</span><br><span class="line">[root@mysql ~]#</span><br></pre></td></tr></table></figure>
<h4 id="14、验证数据库复制的完整性"><a href="#14、验证数据库复制的完整性" class="headerlink" title="14、验证数据库复制的完整性"></a>14、验证数据库复制的完整性</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br></pre></td><td class="code"><pre><span class="line">[root@mysql ~]# pt-table-checksum --user=root --password=123456</span><br><span class="line">Diffs cannot be detected because no slaves were found.  Please read the --recursion-method documentation for information.</span><br><span class="line">            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE</span><br><span class="line">10-26T02:47:01      0      0        3       1       0   0.010 test22.A</span><br><span class="line">10-26T02:47:01      0      0        3       1       0   0.011 test22.B</span><br><span class="line">10-26T02:47:01      0      0        8       1       0   0.025 test22.pet</span><br><span class="line">10-26T02:47:01      0      0        7       1       0   0.011 test22.shop</span><br><span class="line">10-26T02:47:01      0      0        3       1       0   0.008 menagerie.A</span><br><span class="line">10-26T02:47:01      0      0        3       1       0   0.007 menagerie.B</span><br><span class="line">10-26T02:47:01      0      0        9       1       0   0.011 menagerie.employee</span><br><span class="line">10-26T02:47:01      0      0        6       1       0   0.026 menagerie.employee_tbl</span><br><span class="line">10-26T02:47:01      0      0        8       1       0   0.007 menagerie.pet</span><br><span class="line">10-26T02:47:01      0      0        7       1       0   0.006 menagerie.shop</span><br><span class="line">10-26T02:47:01      0      0        0       1       0   0.007 mysql.columns_priv</span><br><span class="line">10-26T02:47:01      0      0        2       1       0   0.008 mysql.db</span><br><span class="line">10-26T02:47:01      0      0        0       1       0   0.006 mysql.event</span><br><span class="line">10-26T02:47:01      0      0        0       1       0   0.006 mysql.func</span><br><span class="line">10-26T02:47:01      0      0       40       1       0   0.023 mysql.help_category</span><br><span class="line">10-26T02:47:01      0      0      608       1       0   0.010 mysql.help_keyword</span><br><span class="line">10-26T02:47:01      0      0     1215       1       0   0.018 mysql.help_relation</span><br><span class="line">10-26T02:47:01      0      0      583       1       0   0.039 mysql.help_topic</span><br><span class="line">10-26T02:47:01      0      0        0       1       0   0.008 mysql.ndb_binlog_index</span><br><span class="line">10-26T02:47:01      0      0        0       1       0   0.007 mysql.plugin</span><br><span class="line">10-26T02:47:01      0      0        6       1       0   0.007 mysql.proc</span><br><span class="line">10-26T02:47:01      0      0        0       1       0   0.010 mysql.procs_priv</span><br><span class="line">10-26T02:47:01      0      0        2       1       0   0.007 mysql.proxies_priv</span><br><span class="line">10-26T02:47:01      0      0        0       1       0   0.027 mysql.servers</span><br><span class="line">10-26T02:47:01      0      0        0       1       0   0.007 mysql.tables_priv</span><br><span class="line">10-26T02:47:01      0      0        0       1       0   0.008 mysql.time_zone</span><br><span class="line">10-26T02:47:01      0      0        0       1       0   0.009 mysql.time_zone_leap_second</span><br><span class="line">10-26T02:47:01      0      0        0       1       0   0.008 mysql.time_zone_name</span><br><span class="line">10-26T02:47:01      0      0        0       1       0   0.010 mysql.time_zone_transition</span><br><span class="line">10-26T02:47:01      0      0        0       1       0   0.011 mysql.time_zone_transition_type</span><br><span class="line">10-26T02:47:01      0      0        5       1       0   0.031 mysql.user</span><br><span class="line">10-26T02:47:01      0      0      200       1       0   0.010 sakila.actor</span><br><span class="line">10-26T02:47:01      0      0      603       1       0   0.013 sakila.address</span><br><span class="line">10-26T02:47:01      0      0       16       1       0   0.011 sakila.category</span><br><span class="line">10-26T02:47:01      0      0      600       1       0   0.012 sakila.city</span><br><span class="line">10-26T02:47:01      0      0      109       1       0   0.014 sakila.country</span><br><span class="line">10-26T02:47:01      0      0      599       1       0   0.011 sakila.customer</span><br><span class="line">10-26T02:47:01      0      0     1000       1       0   0.016 sakila.film</span><br><span class="line">10-26T02:47:01      0      0     5462       1       0   0.020 sakila.film_actor</span><br><span class="line">10-26T02:47:01      0      0     1000       1       0   0.013 sakila.film_category</span><br><span class="line">10-26T02:47:01      0      0     1000       1       0   0.013 sakila.film_text</span><br><span class="line">10-26T02:47:01      0      0     4581       1       0   0.058 sakila.inventory</span><br><span class="line">10-26T02:47:01      0      0        6       1       0   0.008 sakila.language</span><br><span class="line">10-26T02:47:01      0      0    16049       1       0   0.059 sakila.payment</span><br><span class="line">10-26T02:47:02      0      0    16044       1       0   0.069 sakila.rental</span><br><span class="line">10-26T02:47:02      0      0        2       1       0   0.008 sakila.staff</span><br><span class="line">10-26T02:47:02      0      0        2       1       0   0.011 sakila.store</span><br><span class="line">[root@mysql ~]#</span><br></pre></td></tr></table></figure>
<h4 id="15、附录："><a href="#15、附录：" class="headerlink" title="15、附录："></a>15、附录：</h4><p><img src="file:///C:/Users/Deborah/AppData/Local/Temp/msohtmlclip1/01/clip_image020.png" alt=""></p>
<h1 id="6、如何通过慢查日志发现有问题的SQL"><a href="#6、如何通过慢查日志发现有问题的SQL" class="headerlink" title="6、如何通过慢查日志发现有问题的SQL"></a>6、如何通过慢查日志发现有问题的SQL</h1><h2 id="1、查询次数多且每次查询占用时间长的sql"><a href="#1、查询次数多且每次查询占用时间长的sql" class="headerlink" title="1、查询次数多且每次查询占用时间长的sql"></a>1、查询次数多且每次查询占用时间长的sql</h2><p>通常为pt-query-digest分析的前几个查询；该工具可以很清楚的看出每个SQL执行的次数及百分比等信息，执行的次数多，占比比较大的SQL</p>
<h2 id="2、IO大的sql"><a href="#2、IO大的sql" class="headerlink" title="2、IO大的sql"></a>2、IO大的sql</h2><pre><code>注意pt-query-digest分析中的Rows examine项。扫描的行数越多，IO越大。
</code></pre><h2 id="3、未命中的索引的SQL"><a href="#3、未命中的索引的SQL" class="headerlink" title="3、未命中的索引的SQL"></a>3、未命中的索引的SQL</h2><pre><code>注意pt-query-digest分析中的Rows examine 和Rows Send的对比。说明该SQL的索引命中率不高，对于这种SQL，我们要重点进行关注。
</code></pre><h1 id="7、通过explain查询分析SQL的执行计划"><a href="#7、通过explain查询分析SQL的执行计划" class="headerlink" title="7、通过explain查询分析SQL的执行计划"></a>7、通过explain查询分析SQL的执行计划</h1><h2 id="1、使用explain查询SQL的执行计划"><a href="#1、使用explain查询SQL的执行计划" class="headerlink" title="1、使用explain查询SQL的执行计划"></a>1、使用explain查询SQL的执行计划</h2><p>SQL的执行计划侧面反映出了SQL的执行效率，具体执行方式如下所示：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">EXPLAIN SELECT * FROM `user`</span><br><span class="line">  JOIN `post` ON `user`.id = `post`.uid</span><br><span class="line">WHERE user.`created_at` &lt; &apos;2018-10-01 00:00:00&apos; AND `post`.status = 1;</span><br></pre></td></tr></table></figure>
<p>在执行的SQL前面加上explain关键词即可；</p>
<h2 id="2、每个字段的说明："><a href="#2、每个字段的说明：" class="headerlink" title="2、每个字段的说明："></a>2、每个字段的说明：</h2><p>1）id列数字越大越先执行，如果说数字一样大，那么就从上往下依次执行，id列为null的就表是这是一个结果集，不需要使用它来进行查询。</p>
<p>2）select_type列常见的有：</p>
<p>A：simple：表示不需要union操作或者不包含子查询的简单select查询。有连接查询时，外层的查询为simple，且只有一个</p>
<p>B：primary：一个需要union操作或者含有子查询的select，位于最外层的单位查询的select_type即为primary。且只有一个</p>
<p>C：union：union连接的两个select查询，第一个查询是dervied派生表，除了第一个表外，第二个以后的表select_type都是union</p>
<p>D：dependent union：与union一样，出现在union 或unionall语句中，但是这个查询要受到外部查询的影响</p>
<p>E：union result：包含union的结果集，在union和unionall语句中,因为它不需要参与查询，所以id字段为null</p>
<p>F：subquery：除了from字句中包含的子查询外，其他地方出现的子查询都可能是subquery</p>
<p>G：dependentsubquery：与dependent union类似，表示这个subquery的查询要受到外部表查询的影响</p>
<p>H：derived：from字句中出现的子查询，也叫做派生表，其他数据库中可能叫做内联视图或嵌套select</p>
<p>3）table</p>
<p>显示的查询表名，如果查询使用了别名，那么这里显示的是别名，如果不涉及对数据表的操作，那么这显示为null，如果显示为尖括号括起来的<derived n="">就表示这个是临时表，后边的N就是执行计划中的id，表示结果来自于这个查询产生。如果是尖括号括起来的<union m,n="">，与<derived n="">类似，也是一个临时表，表示这个结果来自于union查询的id为M,N的结果集。</derived></union></derived></p>
<p>4）type</p>
<p>依次从好到差：system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery，index_subquery，range，index_merge，index，ALL，除了all之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一个索引</p>
<p>A：system：表中只有一行数据或者是空表，且只能用于myisam和memory表。如果是Innodb引擎表，type列在这个情况通常都是all或者index</p>
<p>B：const：使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type是const。其他数据库也叫做唯一索引扫描</p>
<p>C：eq_ref：出现在要连接过个表的查询计划中，驱动表只返回一行数据，且这行数据是第二个表的主键或者唯一索引，且必须为not null，唯一索引和主键是多列时，只有所有的列都用作比较时才会出现eq_ref</p>
<p>D：ref：不像eq_ref那样要求连接顺序，也没有主键和唯一索引的要求，只要使用相等条件检索时就可能出现，常见与辅助索引的等值查找。或者多列主键、唯一索引中，使用第一个列之外的列作为等值查找也会出现，总之，返回数据不唯一的等值查找就可能出现。</p>
<p>E：fulltext：全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引</p>
<p>F：ref_or_null：与ref方法类似，只是增加了null值的比较。实际用的不多。</p>
<p>G：unique_subquery：用于where中的in形式子查询，子查询返回不重复值唯一值</p>
<p>H：index_subquery：用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重。</p>
<p>I：range：索引范围扫描，常见于使用&gt;,&lt;,is null,between ,in ,like等运算符的查询中。</p>
<p>J：index_merge：表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取所个索引，性能可能大部分时间都不如range</p>
<p>K：index：索引全表扫描，把索引从头到尾扫一遍，常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。 </p>
<p>L：all：这个就是全表扫描数据文件，然后再在server层进行过滤返回符合要求的记录。</p>
<p>5）possible_keys</p>
<p>查询可能使用到的索引都会在这里列出来</p>
<p>6）key</p>
<p>查询真正使用到的索引，select_type为index_merge时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个。</p>
<p>7）key_len</p>
<p>用于处理查询的索引长度，如果是单列索引，那就整个索引长度算进去，如果是多列索引，那么查询不一定都能使用到所有的列，具体使用到了多少个列的索引，这里就会计算进去，没有使用到的列，这里不会计算进去。留意下这个列的值，算一下你的多列索引总长度就知道有没有使用到所有的列了。要注意，mysql的ICP特性使用到的索引不会计入其中。另外，key_len只计算where条件用到的索引长度，而排序和分组就算用到了索引，也不会计算到key_len中。</p>
<p>8）ref</p>
<p>如果是使用的常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func</p>
<p>9）rows</p>
<p>这里是执行计划中估算的扫描行数，不是精确值</p>
<p>10）extra</p>
<p>这个列可以显示的信息非常多，有几十种，常用的有</p>
<p>A：distinct：在select部分使用了distinc关键字</p>
<p>B：no tables used：不带from字句的查询或者From dual查询</p>
<p>C：使用not in()形式子查询或not exists运算符的连接查询，这种叫做反连接。即，一般连接查询是先查询内表，再查询外表，反连接就是先查询外表，再查询内表。</p>
<p>D：using filesort：排序时无法使用到索引时，就会出现这个。常见于order by和group by语句中</p>
<p>E：using index：查询时不需要回表查询，直接通过索引就可以获取查询的数据。</p>
<p>F：using join buffer（block nested loop），using join buffer（batched key accss）：5.6.x之后的版本优化关联查询的BNL，BKA特性。主要是减少内表的循环数量以及比较顺序地扫描查询。</p>
<p>G：using sort_union，using_union，using intersect，using sort_intersection：</p>
<p>using intersect：表示使用and的各个索引的条件时，该信息表示是从处理结果获取交集</p>
<p>using union：表示使用or连接各个使用索引的条件时，该信息表示从处理结果获取并集</p>
<p>using sort_union和usingsort_intersection：与前面两个对应的类似，只是他们是出现在用and和or查询信息量大时，先查询主键，然后进行排序合并后，才能读取记录并返回。</p>
<p>H：using temporary：表示使用了临时表存储中间结果。临时表可以是内存临时表和磁盘临时表，执行计划中看不出来，需要查看status变量，used_tmp_table，used_tmp_disk_table才能看出来。</p>
<p>I：using where：表示存储引擎返回的记录并不是所有的都满足查询条件，需要在server层进行过滤。查询条件中分为限制条件和检查条件，5.6之前，存储引擎只能根据限制条件扫描数据并返回，然后server层根据检查条件进行过滤再返回真正符合查询的数据。5.6.x之后支持ICP特性，可以把检查条件也下推到存储引擎层，不符合检查条件和限制条件的数据，直接不读取，这样就大大减少了存储引擎扫描的记录数量。extra列显示using index condition</p>
<p>J：firstmatch(tb_name)：5.6.x开始引入的优化子查询的新特性之一，常见于where字句含有in()类型的子查询。如果内表的数据量比较大，就可能出现这个</p>
<p>K：loosescan(m..n)：5.6.x之后引入的优化子查询的新特性之一，在in()类型的子查询中，子查询返回的可能有重复记录时，就可能出现这个</p>
<p>除了这些之外，还有很多查询数据字典库，执行计划过程中就发现不可能存在结果的一些提示信息</p>
<p>11）filtered</p>
<p>使用explain extended时会出现这个列，5.7之后的版本默认就有这个字段，不需要使用explain extended了。这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数。</p>
<h2 id="3-具体慢查询的优化案例"><a href="#3-具体慢查询的优化案例" class="headerlink" title="3  具体慢查询的优化案例"></a>3  具体慢查询的优化案例</h2><h3 id="1、Limit查询的优化"><a href="#1、Limit查询的优化" class="headerlink" title="1、Limit查询的优化"></a>1、Limit查询的优化</h3><p>Limit常用于分页处理，时长会伴随orderby从句使用，因此大多时候回使用Filesorts这样会造成大量的IO问题。</p>
<p>优化步骤1：</p>
<p>使用有索引的列或主键进行order by操作，因为大家知道，innodb是按照主键的逻辑顺序进行排序的。可以避免很多的IO操作。</p>
<p>优化步骤2、记录上次返回的主键， 在下次查询时使用主键过滤。（说明：避免了数据量大时扫描过多的记录）</p>
<p>注意事项：</p>
<p>主键要顺序排序并连续的，如果主键中间空缺了某一列，或者某几列，会出现列出数据不足5行的数据；如果不连续的情况，建立一个附加的列index_id列，保证这一列数据要自增的，并添加索引即可。</p>
<h3 id="2、索引的优化"><a href="#2、索引的优化" class="headerlink" title="2、索引的优化"></a>2、索引的优化</h3><h4 id="1、什么是索引？"><a href="#1、什么是索引？" class="headerlink" title="1、什么是索引？"></a>1、什么是索引？</h4><p>索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。</p>
<p>数据库使用索引以找到特定值，然后顺指针找到包含该值的行。在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的ROWID（相当于页码）快速找到表中对应的记录。索引的建立是表中比较有指向性的字段，相当于目录，比如说行政区域代码，同一个地域的行政区域代码都是相同的，那么给这一列加上索引，避免让它重复扫描，从而达到优化的目的！</p>
<h4 id="2、如何创建索引"><a href="#2、如何创建索引" class="headerlink" title="2、如何创建索引"></a>2、如何创建索引</h4><p>在执行CREATE TABLE语句时可以创建索引，也可以单独用CREATE INDEX或ALTER TABLE来为表增加索引。</p>
<pre><code>1、ALTER TABLE
</code></pre><p>ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。</p>
<p>ALTER TABLE table_name ADDINDEX index_name (column_list)</p>
<p>ALTER TABLE table_name ADDUNIQUE (column_list)</p>
<p>ALTER TABLE table_name ADDPRIMARY KEY (column_list)</p>
<p>说明：其中table_name是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。索引名index_name可选，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以在同时创建多个索引。</p>
<pre><code>2、CREATE INDEX
</code></pre><p>CREATE INDEX可对表增加普通索引或UNIQUE索引。</p>
<p>CREATE INDEX index_name ONtable_name (column_list)</p>
<p>CREATE UNIQUE INDEXindex_name ON table_name (column_list)</p>
<pre><code>说明：table_name、index_name和column_list具有与ALTER TABLE语句中相同的含义，索引名不可选。另外，不能用CREATE INDEX语句创建PRIMARY KEY索引。



3、索引类型
</code></pre><p>在创建索引时，可以规定索引能否包含重复值。如果不包含，则索引应该创建为PRIMARY KEY或UNIQUE索引。对于单列惟一性索引，这保证单列不包含重复的值。对于多列惟一性索引，保证多个值的组合不重复。</p>
<p>PRIMARY KEY索引和UNIQUE索引非常类似。</p>
<p>事实上，PRIMARY KEY索引仅是一个具有名称PRIMARY的UNIQUE索引。这表示一个表只能包含一个PRIMARY KEY，因为一个表中不可能具有两个同名的索引。</p>
<p>下面的SQL语句对students表在sid上添加PRIMARYKEY索引。</p>
<p>ALTER TABLE students ADDPRIMARY KEY (sid)</p>
<pre><code>4、删除索引

       可利用ALTER TABLE或DROPINDEX语句来删除索引。类似于CREATE INDEX语句，DROPINDEX可以在ALTER TABLE内部作为一条语句处理，语法如下。
</code></pre><p>DROP INDEX index_name ONtalbe_name</p>
<p>ALTER TABLE table_name DROPINDEX index_name</p>
<p>ALTER TABLE table_name DROPPRIMARY KEY</p>
<p>其中，前两条语句是等价的，删除掉table_name中的索引index_name。</p>
<p>第3条语句只在删除PRIMARYKEY索引时使用，因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。如果没有创建PRIMARY KEY索引，但表具有一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引。</p>
<p>如果从表中删除了某列，则索引会受到影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。</p>
<p>   5、查看索引</p>
<p>   6、什么情况下，使用索引了？</p>
<pre><code>1、表的主关键字
</code></pre><p>2、自动建立唯一索引</p>
<p>3、表的字段唯一约束</p>
<p>4、直接条件查询的字段（在SQL中用于条件约束的字段）</p>
<p>5、查询中与其它表关联的字段</p>
<p>6、查询中排序的字段（排序的字段如果通过索引去访问那将大大提高排序速度）</p>
<p>7、查询中统计或分组统计的字段</p>
<p>8、表记录太少（如果一个表只有5条记录，采用索引去访问记录的话，那首先需访问索引表，再通过索引表访问数据表，一般索引表与数据表不在同一个数据块）</p>
<p>9、经常插入、删除、修改的表（对一些经常处理的业务表应在查询允许的情况下尽量减少索引）</p>
<p>10、数据重复且分布平均的表字段（假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。）</p>
<p>11、经常和主字段一块查询但主字段索引值比较多的表字段</p>
<p>12、对千万级MySQL数据库建立索引的事项及提高性能的手段</p>
<h4 id="3、如何选择合适的列建立索引"><a href="#3、如何选择合适的列建立索引" class="headerlink" title="3、如何选择合适的列建立索引"></a>3、如何选择合适的列建立索引</h4><pre><code>1、在where从句，group by从句，order by从句，on从句中虚线的列添加索引

2、索引字段越小越好（因为数据库数据存储单位是以“页”为单位的，数据存储的越多，IO也会越大）
</code></pre><p>3、离散度大的列放到联合索引的前面</p>
<p>例子：</p>
<p>注意:</p>
<pre><code>是index（staff_id，customer_id）好，还是index（customer_id，staff_id）好
</code></pre><p>那我们怎么进行验证离散度好了？</p>
<p>A、我们先查看一下表结构</p>
<p><img src="file:///C:/Users/Deborah/AppData/Local/Temp/msohtmlclip1/01/clip_image087.jpg" alt=""></p>
<p>B、分别查看这两个字段中不同的id的数量，数量越多，则表明离散程度越大：因此可以通过下图看出：customer_id 离散程度大。</p>
<p><img src="file:///C:/Users/Deborah/AppData/Local/Temp/msohtmlclip1/01/clip_image089.jpg" alt=""></p>
<p>结论：由于customer_id 离散程度大，使用index（customer_id，staff_id）好</p>
<p>C、mysql联合索引</p>
<p>①命名规则 ：表名_字段名</p>
<pre><code>1、需要加索引的字段，要在where条件中
</code></pre><p>2、数据量少的字段不需要加索引</p>
<p>3、如果where条件中是OR关系，加索引不起作用</p>
<p>4、符合最左原则</p>
<p>②什么是联合索引</p>
<p>1、两个或更多个列上的索引被称作联合索引，又被称为是复合索引。</p>
<p>2、利用索引中的附加列，您可以缩小搜索的范围，但使用一个具有两列的索引不同于使用两个单独的索引。复合索引的结构与电话簿类似，人名由姓和名构成，电话簿首先按姓氏对进行排序，然后按名字对有相同姓氏的人进行排序。如果您知 道姓，电话簿将非常有用；如果您知道姓和名，电话簿则更为有用，但如果您只知道名不姓，电话簿将没有用处。</p>
<p>所以说创建复合索引时，应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时，复合索引非常有用；仅对后面的任意列执行搜索时，复合索引则没有用处。</p>
<h4 id="4、索引优化SQL的方法"><a href="#4、索引优化SQL的方法" class="headerlink" title="4、索引优化SQL的方法"></a>4、索引优化SQL的方法</h4><p>1、索引的维护及优化（重复及冗余索引）</p>
<pre><code>增加索引会有利于查询效率，但会降低insert，update，delete的效率，但实际上往往不是这样的，过多的索引会不但会影响使用效率，同时会影响查询效率，这是由于数据库进行查询分析时，首先要选择使用哪一个索引进行查询，如果索引过多，分析过程就会越慢，这样同样的减少查询的效率，因此我们要知道如何增加，有时候要知道维护和删除不需要的索引
</code></pre><p>2、如何找到重复和冗余的索引</p>
<p>重复索引：</p>
<p>重复索引是指相同的列以相同的顺序简历的同类型的索引，如下表中的 primary key和ID列上的索引就是重复索引</p>
<p>冗余索引：</p>
<pre><code>冗余索引是指多个索引的前缀列相同，或是在联合索引中包含了主键的索引，下面这个例子中key（name，id）就是一个冗余索引。
</code></pre><p>说明：对于innodb来说，每一个索引后面，实际上都会包含主键，这时候我们建立的联合索引，又人为的把主键包含进去，那么这个时候就是一个冗余索引。</p>
<p>3、如何查找重复索引</p>
<p>工具：使用pt-duplicate-key-checker工具检查重复及冗余索引</p>
<p><img src="file:///C:/Users/Deborah/AppData/Local/Temp/msohtmlclip1/01/clip_image091.jpg" alt=""></p>
<p>4、索引维护的方法</p>
<pre><code>由于业务变更，某些索引是后续不需要使用的，就要进行杀出。
</code></pre><p>在mysql中，目前只能通过慢查询日志配合pt-index-usage工具来进行索引使用情况的分析；</p>
<p><img src="file:///C:/Users/Deborah/AppData/Local/Temp/msohtmlclip1/01/clip_image093.jpg" alt=""></p>
<p>附：<a href="https://www.percona.com/downloads/" target="_blank" rel="noopener">https://www.percona.com/downloads/</a></p>
<h4 id="5、注意事项"><a href="#5、注意事项" class="headerlink" title="5、注意事项"></a>5、注意事项</h4><p>设计好MySql的索引可以让你的数据库飞起来，大大的提高数据库效率。设计MySql索引的时候有一下几点注意：</p>
<p>1，创建索引</p>
<p>对于查询占主要的应用来说，索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的，或者说没有添加更为有效的索引导致。如果不加</p>
<p>索引的话，那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描，如果一张表的数据量很大而符合条件的结果又很少，那么不加索引会引起致命的性能下降。</p>
<p>但是也不是什么情况都非得建索引不可，比如性别可能就只有两个值，建索引不仅没什么优势，还会影响到更新速度，这被称为过度索引。</p>
<p>2，复合索引</p>
<p>比如有一条语句是这样的：select * from users where area=’beijing’ and age=22;</p>
<p>如果我们是在area和age上分别创建单个索引的话，由于mysql查询每次只能使用一个索引，所以虽然这样已经相对不做索引时全表扫描提高了很多效</p>
<p>率，但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area, age,salary)的复合索引，那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引，这被称为最佳左前缀特性。</p>
<p>因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。</p>
<p>3，索引不会包含有NULL值的列</p>
<p>只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。</p>
<p>4，使用短索引</p>
<p>对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的 列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。</p>
<p>5，排序的索引问题</p>
<p>mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。</p>
<p>6，like语句操作</p>
<p>一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。</p>
<p>7，不要在列上进行运算</p>
<p>select* from users where</p>
<p>YEAR(adddate)</p>
<p>8，不使用NOT IN和操作</p>
<p>NOTIN和操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替，id3则可使用id&gt;3 or id</p>

      
    </div>
    <footer class="article-footer">
      <a data-url="http://yoursite.com/2019/01/14/mysql/sql_数据库性能优化/" data-id="ckcc5iwq2009jq4uwbk77h0uz" class="article-share-link">Teilen</a>
      
      
  <ul class="article-tag-list"><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/mysql/">mysql</a></li></ul>

    </footer>
  </div>
  
    
<nav id="article-nav">
  
    <a href="/2019/01/24/Spring Boot缓存注解/" id="article-nav-newer" class="article-nav-link-wrap">
      <strong class="article-nav-caption">Neuer</strong>
      <div class="article-nav-title">
        
          Spring Boot缓存注解
        
      </div>
    </a>
  
  
    <a href="/2019/01/14/mysql/sql_数据库进阶/" id="article-nav-older" class="article-nav-link-wrap">
      <strong class="article-nav-caption">Älter</strong>
      <div class="article-nav-title">数据库-进阶</div>
    </a>
  
</nav>

  
</article>

</section>
        
          <aside id="sidebar">
  
    

  
    
  <div class="widget-wrap">
    <h3 class="widget-title">Tags</h3>
    <div class="widget">
      <ul class="tag-list"><li class="tag-list-item"><a class="tag-list-link" href="/tags/Design-Patterns/">Design Patterns</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/Git/">Git</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/Html/">Html</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/Http/">Http</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/JavaScript/">JavaScript</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/Sort/">Sort</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/algorithm/">algorithm</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/ali-weixin-pay/">ali/weixin pay</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/docker/">docker</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/elasticsearch/">elasticsearch</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/go/">go</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/hexo/">hexo</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/idea/">idea</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/java/">java</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/jvm/">jvm</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/linux/">linux</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/logstash/">logstash</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/mysql/">mysql</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/php/">php</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/pytest/">pytest</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/python/">python</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/springboot/">springboot</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/util/">util</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/vue/">vue</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/wechat/">wechat</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/我的世界/">我的世界</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/消息队列/">消息队列</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/线程/">线程</a></li></ul>
    </div>
  </div>


  
    
  <div class="widget-wrap">
    <h3 class="widget-title">Tag Cloud</h3>
    <div class="widget tagcloud">
      <a href="/tags/Design-Patterns/" style="font-size: 16.36px;">Design Patterns</a> <a href="/tags/Git/" style="font-size: 10px;">Git</a> <a href="/tags/Html/" style="font-size: 10px;">Html</a> <a href="/tags/Http/" style="font-size: 12.73px;">Http</a> <a href="/tags/JavaScript/" style="font-size: 15.45px;">JavaScript</a> <a href="/tags/Sort/" style="font-size: 18.18px;">Sort</a> <a href="/tags/algorithm/" style="font-size: 11.82px;">algorithm</a> <a href="/tags/ali-weixin-pay/" style="font-size: 13.64px;">ali/weixin pay</a> <a href="/tags/docker/" style="font-size: 17.27px;">docker</a> <a href="/tags/elasticsearch/" style="font-size: 10px;">elasticsearch</a> <a href="/tags/go/" style="font-size: 11.82px;">go</a> <a href="/tags/hexo/" style="font-size: 10px;">hexo</a> <a href="/tags/idea/" style="font-size: 10px;">idea</a> <a href="/tags/java/" style="font-size: 20px;">java</a> <a href="/tags/jvm/" style="font-size: 11.82px;">jvm</a> <a href="/tags/linux/" style="font-size: 18.18px;">linux</a> <a href="/tags/logstash/" style="font-size: 12.73px;">logstash</a> <a href="/tags/mysql/" style="font-size: 14.55px;">mysql</a> <a href="/tags/php/" style="font-size: 11.82px;">php</a> <a href="/tags/pytest/" style="font-size: 10px;">pytest</a> <a href="/tags/python/" style="font-size: 13.64px;">python</a> <a href="/tags/springboot/" style="font-size: 17.27px;">springboot</a> <a href="/tags/util/" style="font-size: 19.09px;">util</a> <a href="/tags/vue/" style="font-size: 10px;">vue</a> <a href="/tags/wechat/" style="font-size: 10.91px;">wechat</a> <a href="/tags/我的世界/" style="font-size: 10px;">我的世界</a> <a href="/tags/消息队列/" style="font-size: 10.91px;">消息队列</a> <a href="/tags/线程/" style="font-size: 11.82px;">线程</a>
    </div>
  </div>

  
    
  <div class="widget-wrap">
    <h3 class="widget-title">Archiv</h3>
    <div class="widget">
      <ul class="archive-list"><li class="archive-list-item"><a class="archive-list-link" href="/archives/2020/05/">May 2020</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2020/04/">April 2020</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2020/03/">March 2020</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2020/01/">January 2020</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/10/">October 2019</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/08/">August 2019</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/06/">June 2019</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/04/">April 2019</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/03/">March 2019</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/02/">February 2019</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/01/">January 2019</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2018/12/">December 2018</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2018/11/">November 2018</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2018/10/">October 2018</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2018/09/">September 2018</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2018/07/">July 2018</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2018/06/">June 2018</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2018/05/">May 2018</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2018/04/">April 2018</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2018/03/">March 2018</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2018/02/">February 2018</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2018/01/">January 2018</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2017/12/">December 2017</a></li></ul>
    </div>
  </div>


  
    
  <div class="widget-wrap">
    <h3 class="widget-title">letzter Beitrag</h3>
    <div class="widget">
      <ul>
        
          <li>
            <a href="/2020/05/17/linux/linux_shell/">linux—shell</a>
          </li>
        
          <li>
            <a href="/2020/05/16/linux/linux_awk_sed/">linux-awk\sed 示例</a>
          </li>
        
          <li>
            <a href="/2020/04/11/linux/linux_bash/">linux—bash</a>
          </li>
        
          <li>
            <a href="/2020/03/29/hello-world/">Hello World</a>
          </li>
        
          <li>
            <a href="/2020/03/16/go基础/">go基础知识</a>
          </li>
        
      </ul>
    </div>
  </div>

  
</aside>
        
      </div>
      <footer id="footer">
  
  <div class="outer">
    <div id="footer-info" class="inner">
      &copy; 2020 vchen<br>
      Powered by <a href="http://hexo.io/" target="_blank">Hexo</a>
    </div>
  </div>
</footer>
    </div>
    <nav id="mobile-nav">
  
    <a href="/" class="mobile-nav-link">Home</a>
  
    <a href="/archives" class="mobile-nav-link">Archives</a>
  
</nav>
    

<script src="//ajax.googleapis.com/ajax/libs/jquery/2.0.3/jquery.min.js"></script>


  <link rel="stylesheet" href="/fancybox/jquery.fancybox.css">
  <script src="/fancybox/jquery.fancybox.pack.js"></script>


<script src="/js/script.js"></script>



  </div>
</body>
</html>